By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Ranking on a subset of a query

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"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.





Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.