Cumulate By

[Bill]

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[KARL DEWEY]

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

 

[Bill]

That produces some results but it does not account for ties in the data, also
i would prefer to rank descending.

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

--
KARL DEWEY
Build a little - Test a little

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[Bill]

The tie breaker for ties would be the date, so which ever tie temperature
happened first chronologically would get a higher rank.

That produces some results but it does not account for ties in the data, also
i would prefer to rank descending.

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

--
KARL DEWEY
Build a little - Test a little

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[KARL DEWEY]

Try this ---
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) < Q.[Avg]+(Q.[dt]/100000))+1 AS Rank
FROM Test_1 AS Q
ORDER BY Year(Q.[dt]), Q.[Avg]+(Q.[dt]/100000);

--
KARL DEWEY
Build a little - Test a little

The tie breaker for ties would be the date, so which ever tie temperature
happened first chronologically would get a higher rank.

That produces some results but it does not account for ties in the data, also
i would prefer to rank descending.

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

--
KARL DEWEY
Build a little - Test a little


:

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[Bill]

that is better but i still need the rank descending. By descending i mean
the highest temperature has a rank of 1 then the ties are solved by the
chronologically first getting a higher rank (which is actually a lower
number). also what would i need to do to add an extra group of weather
station.

Try this ---
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) < Q.[Avg]+(Q.[dt]/100000))+1 AS Rank
FROM Test_1 AS Q
ORDER BY Year(Q.[dt]), Q.[Avg]+(Q.[dt]/100000);

--
KARL DEWEY
Build a little - Test a little

The tie breaker for ties would be the date, so which ever tie temperature
happened first chronologically would get a higher rank.

That produces some results but it does not account for ties in the data, also
i would prefer to rank descending.

:

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

--
KARL DEWEY
Build a little - Test a little


:

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[Bill]

This query ran all night without completely finishing. Is there a faster
method. I thought a simple cumulator would be pretty fast once the data was
sorted correctly. start at 1 count, add one until the year changes, start
over at 1 and keep going. I have a macro that does that in less than 1
minute but i wanted to get it in a query.

 

[KARL DEWEY]

i still need the rank descending.
Post an example of how you want the display to look.
What is the field name and datatype?

 

[Bill]

field name is stationid and it is text

Post an example of how you want the display to look.

What is the field name and datatype?

 

[KARL DEWEY]

i still need the rank descending.
Post an example of how you want the display to look.

 

[John Spencer]

I think that if you change the less than to greater Than in the subquery you
will get the ranking you desire.

Adding another weatherstation would mean you would need to add that to the
WHERE clause of the subquery. Assuming the field for identifying the weather
station was WXID, your subquery would look like the following:

(SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) > Q.[Avg]+(Q.[dt]/100000)
AND Q1.WXID + Q.WXID)+1 AS Rank

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

that is better but i still need the rank descending. By descending i mean
the highest temperature has a rank of 1 then the ties are solved by the
chronologically first getting a higher rank (which is actually a lower
number). also what would i need to do to add an extra group of weather
station.

Try this ---
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) < Q.[Avg]+(Q.[dt]/100000))+1 AS Rank
FROM Test_1 AS Q
ORDER BY Year(Q.[dt]), Q.[Avg]+(Q.[dt]/100000);

--
KARL DEWEY
Build a little - Test a little

The tie breaker for ties would be the date, so which ever tie temperature
happened first chronologically would get a higher rank.

:

That produces some results but it does not account for ties in the data, also
i would prefer to rank descending.

:

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

--
KARL DEWEY
Build a little - Test a little


:

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[Bill]

it seems to be correct but the ties are being done backwards

I think that if you change the less than to greater Than in the subquery you
will get the ranking you desire.

Adding another weatherstation would mean you would need to add that to the
WHERE clause of the subquery. Assuming the field for identifying the weather
station was WXID, your subquery would look like the following:

(SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) > Q.[Avg]+(Q.[dt]/100000)
AND Q1.WXID + Q.WXID)+1 AS Rank

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

that is better but i still need the rank descending. By descending i mean
the highest temperature has a rank of 1 then the ties are solved by the
chronologically first getting a higher rank (which is actually a lower
number). also what would i need to do to add an extra group of weather
station.

Try this ---
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) < Q.[Avg]+(Q.[dt]/100000))+1 AS Rank
FROM Test_1 AS Q
ORDER BY Year(Q.[dt]), Q.[Avg]+(Q.[dt]/100000);

--
KARL DEWEY
Build a little - Test a little


:

The tie breaker for ties would be the date, so which ever tie temperature
happened first chronologically would get a higher rank.

:

That produces some results but it does not account for ties in the data, also
i would prefer to rank descending.

:

Try this --
SELECT Year(Q.[dt]) AS CY_Year, Q.dt, Q.Avg, (SELECT COUNT(*) FROM [Test] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] < Q.[Avg])+1 AS Rank
FROM Test AS Q
ORDER BY Year(Q.[dt]), Q.Avg DESC;

--
KARL DEWEY
Build a little - Test a little


:

I am trying to create a query that effectively ranks temperature from highest
to lowest by year. I need a rank assigned by year 1-365 (366 for leap
years). I have a table with the actual data and i can easily sort the data
correclty but I need to have a field with the rank by year. below is how far
i have gotten.

SELECT Year([dt]) AS [year], test.avg, test.Dt
FROM test
ORDER BY Year([dt]), test.avg DESC , test.Dt;

 

[Lord Kelvan]

the eaisest way would be to query the query and order by rank but its
up to you

Regards
Kelvan

 

[John Spencer]

Whoops! Got a plus where I should have an equal


(SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) > Q.[Avg]+(Q.[dt]/100000)
AND Q1.WXID = Q.WXID)+1 AS Rank

As far as the times being backward, I don't have a solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

Try changing this way --
AND Q1.[Avg] +(1/Q1.[dt]) > Q.[Avg]+(1/Q.[dt])

and in the Order BY also.
--
KARL DEWEY
Build a little - Test a little

Whoops! Got a plus where I should have an equal


(SELECT COUNT(*) FROM
[Test_1] Q1
WHERE Year(Q1.[dt]) = Year(Q.[dt])
AND Q1.[Avg] +(Q1.[dt]/100000) > Q.[Avg]+(Q.[dt]/100000)
AND Q1.WXID = Q.WXID)+1 AS Rank

As far as the times being backward, I don't have a solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

it seems to be correct but the ties are being done backwards