Top 20 by group

[jmersing]

I have a somewhat basic query, lets say its like this

select top 20 onefield, twofield, three field
from my table

This returns twenty records.

There are three types of values in onefield, I want to see the top twenty for each i.e. returning 60 rows.

Can't figure it out?

 

[jbeaucaire]

Can you show us some actual data and the actual layout of the results? That would make formulating the formulas/macros much simpler.

 

[Richard Schollar]

I presume you could simply do something like this:

SELECT TOP 20 One, Two, Three FROM myTable WHERE One = 'firstvalue'
UNION
SELECT TOP 20 One, Two, Three FROM myTable WHERE One = 'secondvalue'
UNION
SELECT TOP 20 One, Two, Three FROM myTable WHERE One = 'thirdvalue'

 

[jmersing]

Thanks I'll give that a try

 

[Fazza]

With a slight edit to Richard's SQL

SELECT TOP 20 One, Two, Three
FROM myTable
WHERE One = 'firstvalue'
ORDER BY One DESC
 
UNION
SELECT TOP 20 One, Two, Three
FROM myTable
WHERE One = 'secondvalue'
ORDER BY One DESC
 
UNION
SELECT TOP 20 One, Two, Three
FROM myTable
WHERE One = 'thirdvalue'
ORDER BY One DESC

Note 'firstvalue' assumes a text entry in field One. If it is numeric, change from
WHERE One = 'firsttext'
to
WHERE One = 123

HTH, Fazza

 

[jmersing]

The previous suggestion worked well. Thanks for all who replied.