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

Top 4 values based on a group

P: 38
I am working on a query that I would like to get the top 4 values based on a group. Here is the SQL:

SELECT qRenPercRept1.CountOfccName, qRenPercRept1.Merged, qRenPercRept1.ccName, qRenPercRept1.AvgOfcpUpcomingRen
FROM qRenPercRept1
ORDER BY qRenPercRept1.CountOfccName DESC;

I want the top 4 Merged based on Countofccname.

is this possible? I've tried a few different things that I have read online, but I keep getting "Data mistype" errors.

Thanks so much in advance!

LSGKelly
Feb 8 '10 #1
Share this Question
Share on Google+
7 Replies


yarbrough40
100+
P: 320
is this what you want?..... what do you mean by "merged"?
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 4 qRenPercRept1.CountOfccName, qRenPercRept1.Merged, qRenPercRept1.ccName, qRenPercRept1.AvgOfcpUpcomingRen
  2. FROM qRenPercRept1
  3. ORDER BY qRenPercRept1.CountOfccName DESC;
  4.  
Feb 8 '10 #2

P: 38
No, this is not what I need. I have two columns, one has the total amount of carriers [countofccname] and the other is called Merged, which is a field that has a list of Markets. What I need is the top four [countofccname] per Market.

It looks like this:

Atlanta, 32
Atlanta, 30
Atlanta, 25
Atlanta, 6
Atlanta, 1
Baltimore, 34
Baltimore, 32
Baltimore, 3
Baltimore, 2
Baltimore, 1

With Atlanta/Baltimore being the Merged field and the number representing [countofccname]

What I would like is for it to show me only the top four for each market, so my query would only pull the following:

Atlanta, 32
Atlanta, 30
Atlanta, 25
Atlanta, 6
Baltimore, 34
Baltimore, 32
Baltimore, 3
Baltimore, 2

There are also one other field in the query, AvgOfcpUpcomingRen which needs to be there.

Thanks again for your help!
Feb 8 '10 #3

yarbrough40
100+
P: 320
Expand|Select|Wrap|Line Numbers
  1. SELECT top 4 qRenPercRept1.Merged, qRenPercRept1.countofccname
  2. FROM qRenPercRept1 
  3. Where qRenPercRept1.Merged = "Atlanta"
  4. UNION ALL
  5. SELECT top 4 qRenPercRept1.Merged, qRenPercRept1.countofccname
  6. FROM qRenPercRept1 
  7. Where qRenPercRept1.Merged = "Baltimore"
  8. ORDER BY qRenPercRept1.Merged, qRenPercRept1.CountOfccName DESC
  9.  
  10.  
Feb 8 '10 #4

P: 38
I think we are getting closer, except Atlanta and Baltimore are just examples of the names in Merged. There are actually about 30 different Markets.
Feb 8 '10 #5

yarbrough40
100+
P: 320
well you could keep adding unions all down the page to include all 30 markets. Access could run a query like that without breaking a sweat. The only other way is to use VBA to build two recordsets, one like so:
SELECT distinct Merged
FROM qRenPercRept1

this will get your unique markets. store that value in a variable and loop through them while applying that variable into your UNION query recordset.
Feb 8 '10 #6

P: 38
Thank you so much for your help! I did it the first way. Works like a charm.

:)
Feb 8 '10 #7

P: 74
Absolute genius...."SELECT TOP 4" ....i really gotta read more about SQL, bc not knowing that things like that are capable, are really slowing me down i am sure.
Feb 8 '10 #8

Post your reply

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