How to rank

  • Thread starter Thread starter Shezan Rahman
  • Start date Start date
S

Shezan Rahman

DATE RATE
23-Jul-04 40.99
23-Jul-04 40.99
23-Jul-04 40.99
23-Jul-04 44.99
23-Jul-04 45.95
23-Jul-04 45.99
23-Jul-04 45.99
24-Jul-04 40.99
24-Jul-04 40.99
24-Jul-04 40.99
24-Jul-04 44.99
24-Jul-04 45.4
24-Jul-04 45.95
24-Jul-04 45.99
24-Jul-04 45.99
25-Jul-04 30.4
25-Jul-04 32.91
25-Jul-04 37.01
25-Jul-04 37.01
25-Jul-04 45.99
25-Jul-04 45.99
25-Jul-04 46.95
25-Jul-04 48.99
25-Jul-04 59.95
25-Jul-04 64.24

How do I add another field so that the data will look like
the following:
DATE RATE Rank
23-Jul-04 40.99 1
23-Jul-04 40.99 1
23-Jul-04 40.99 1
23-Jul-04 44.99 4
23-Jul-04 45.95 5
23-Jul-04 45.99 6
23-Jul-04 45.99 7
24-Jul-04 40.99 1
24-Jul-04 40.99 1
24-Jul-04 40.99 1
24-Jul-04 44.99 4
24-Jul-04 45.4 5
24-Jul-04 45.95 6
24-Jul-04 45.99 7
24-Jul-04 45.99 7
25-Jul-04 30.4 1
25-Jul-04 32.91 2
25-Jul-04 37.01 3
25-Jul-04 37.01 3
25-Jul-04 45.99 5
25-Jul-04 45.99 5
25-Jul-04 46.95 7
25-Jul-04 48.99 8
25-Jul-04 59.95 9
25-Jul-04 64.24 10

Thanks for your help
 
Dear Shezan:

Use a subquery that COUNTs the number of rows with a lesser RATE
value.

If you'll send me the SQL for what you have so far, I'll add this and
post back here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I just extracted the date and the rate field from another
table and then sorted the data and group by.

Here's the code:

SELECT test1.PICK_UP, Count(test1.RATE_2) AS
CountOfRATE_2, test1.RATE_2
FROM test1
GROUP BY test1.PICK_UP, test1.RATE_2
ORDER BY test1.PICK_UP, Count(test1.RATE_2) DESC;

Thanks in advance.
 
Dear Shezan:

I've removed the CountOfRate column for simplicity. You should be
able to add it back later. You didn't have that in your sample data,
and it was confusing.

SELECT PICK_UP, RATE_2,
(SELECT COUNT(*) + 1 From test1 T1
WHERE T1.PICK_UP = T.PICK_UP
AND T1.RATE_2 < T.RATE_2) AS Rank
FROM test1 T
ORDER BY PICK_UP DESC;

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top