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

Need to rank top 3 WITHIN groups

P: 2
I have a query that I need to show the top 3 within each grouping. I have several distance combinations for Employees and Facilities based on distance between zip codes. I need to get the lowest 3 distances for each employee. (some employees may have only 1 possible record, while others will have more than 5). I have Emp_ID, HomeZip, FacilityZip, Distance --
#11111, Betty, 64015, 64052, 25
#11111, Betty, 64015, 64054, 50
#11111, Betty, 64015, 64057, 75
#11111, Betty, 64015, 65044, 100
#11111, Betty, 64015, 65045, 112
#11111, Betty, 64015, 64050, 120
#22222, Tom, 74015, 74052, 35
#22222, Tom, 74015, 74054, 70
#22222, Tom, 74015, 76057, 75
#22222, Tom, 74015, 75044, 123
#22222, Tom, 74015, 75045, 212
#22222, Tom, 74015, 74050, 320
#22222, Tom, 74015, 48000, 274
... etc.
So I'd get the lowest 3 distances for #11111, Betty and then the lowest 3 for #22222, Tom and so on. Anyone out there able to help me? Thanks!
Dec 17 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,383
I have a query that I need to show the top 3 within each grouping. I have several distance combinations for Employees and Facilities based on distance between zip codes. I need to get the lowest 3 distances for each employee. (some employees may have only 1 possible record, while others will have more than 5). I have Emp_ID, HomeZip, FacilityZip, Distance --
#11111, Betty, 64015, 64052, 25
#11111, Betty, 64015, 64054, 50
#11111, Betty, 64015, 64057, 75
#11111, Betty, 64015, 65044, 100
#11111, Betty, 64015, 65045, 112
#11111, Betty, 64015, 64050, 120
#22222, Tom, 74015, 74052, 35
#22222, Tom, 74015, 74054, 70
#22222, Tom, 74015, 76057, 75
#22222, Tom, 74015, 75044, 123
#22222, Tom, 74015, 75045, 212
#22222, Tom, 74015, 74050, 320
#22222, Tom, 74015, 48000, 274
... etc.
So I'd get the lowest 3 distances for #11111, Betty and then the lowest 3 for #22222, Tom and so on. Anyone out there able to help me? Thanks!
Use a subquery to return criteria by selecting the top 3 sorting by ascending distance.
Dec 17 '07 #2

P: 2
How do I do that? When I try to do a Top 3 query, I get the top 3 for all of the records. I want the top 3 for each grouping. How can I do this in either the query design or sql statement? Any sample code you can provide using this data I have would be much appreciated. Thanks!
Dec 18 '07 #3

Rabbit
Expert Mod 10K+
P: 12,383
No no, you use the Top 3 query in a subquery as part of the WHERE criteria.
Dec 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.