Ranking on a subset of a query

[Joseph Bloch]

In all the threads on ranking in Access queries, I've not seen
anything that deals with ranking against a subset of the query
results. What I need to do is take the following query results:

Dept Subdept Amount
AAA A1 75
AAA A2 13
AAA A3 45
BBB B1 4
BBB B2 16
CCC C1 20
CCC C2 22
CCC C3 15

And rank the Subdept field _within_ the Dept field, so I would get the
following results:

Dept Subdept Amount Rank
AAA A1 75 1
AAA A2 13 3
AAA A3 45 2
BBB B1 4 2
BBB B2 16 1
CCC C1 20 2
CCC C2 22 1
CCC C3 15 3

I am completely stumped by this. Any help would be MOST appreciated!

Joe

[John Winterbottom]

"Joseph Bloch" <jb****@surveyusa.com> wrote in message
news:df*************************@posting.google.co m...

In all the threads on ranking in Access queries, I've not seen
anything that deals with ranking against a subset of the query
results. What I need to do is take the following query results:

Dept Subdept Amount
AAA A1 75
AAA A2 13
AAA A3 45
BBB B1 4
BBB B2 16
CCC C1 20
CCC C2 22
CCC C3 15

And rank the Subdept field _within_ the Dept field, so I would get the
following results:

Dept Subdept Amount Rank
AAA A1 75 1
AAA A2 13 3
AAA A3 45 2
BBB B1 4 2
BBB B2 16 1
CCC C1 20 2
CCC C2 22 1
CCC C3 15 3

Here is one way:

select a.Dept, a.Subdept, a.Amount, count(*) as rank
from
(
select Dept, Subdept, Amount
from joe
group by Dept, Subdept, Amount
) as a
inner join
(
select Dept, Subdept, Amount
from joe
group by Dept, Subdept, Amount
) as b on a.Dept = b.Dept and a.Amount <= b.Amount
group by a.Dept, a.Subdept, a.Amount
order by a.Dept, a.Subdept, count(*)
You should be aware of how this handles ties, (if two rows tie for 1st place
it will give them each a ranking of 2). If that's not the behaviour you need
then you will have to modify the query or use a different method.