# Top 20 by group

#### jmersing

##### Well-known Member
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?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Can you show us some actual data and the actual layout of the results? That would make formulating the formulas/macros much simpler.

I presume you could simply do something like this:

Code:
``````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'``````

Thanks I'll give that a try

With a slight edit to Richard's SQL

Code:
``````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

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

Replies
11
Views
433
Replies
12
Views
615
Replies
5
Views
2K
Replies
15
Views
943
Replies
0
Views
541

1,196,266
Messages
6,014,333
Members
441,816
Latest member
Klingon1960

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back