Top 4 values based on a group

[LSGKelly]

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

[yarbrough40]

is this what you want?..... what do you mean by "merged"?

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

[LSGKelly]

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!

[yarbrough40]

SELECT top 4 qRenPercRept1.Merged, qRenPercRept1.countofccname
FROM qRenPercRept1 
Where qRenPercRept1.Merged = "Atlanta"
UNION ALL
SELECT top 4 qRenPercRept1.Merged, qRenPercRept1.countofccname
FROM qRenPercRept1 
Where qRenPercRept1.Merged = "Baltimore"
ORDER BY qRenPercRept1.Merged, qRenPercRept1.CountOfccName DESC
 
 

[LSGKelly]

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.

[yarbrough40]

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.

[LSGKelly]

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

:)

[kstevens]

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.