Show first X rows for each value in field Y

  • Thread starter Thread starter Paul Blay
  • Start date Start date
P

Paul Blay

Thanks to the 'Find duplicates' wizard it's easy to show records where 6 or less
records have the same value in a field
(
In (SELECT [y] FROM
As Tmp GROUP BY [y] HAVING Count(*)<7 )
in the criteria)

However what I want to do is show the first 6 records whether or not there are
more than 6 records having a certain value.

Is there an equivalent SQL expression that will do the job I'm after?
 
Try the TOP key word.
Click to expand...

Well that gives me something to feed to Google anyway.

So far I've got it returning the records containing the six highest values
in the field [y], but what I'm after is the first six rows for every value
in the field [y].

(e.g. if I had
1
1
1
1
1
1
2
2
2
3
3
4
and I wanted the first two records for every value I'd want
1
1
2
2
3
3
4
back.

It's quite possible I'm missing something as I'm very much a beginner at
SQL, but if you could provide a further hint ...
Paul Blay said:
Thanks to the 'Find duplicates' wizard it's easy to show records where 6
or less
records have the same value in a field
(
In (SELECT [y] FROM
As Tmp GROUP BY [y] HAVING Count(*)<7 )
in the criteria)

However what I want to do is show the first 6 records whether or not there
are more than 6 records having a certain value.

Is there an equivalent SQL expression that will do the job I'm after?
Click to expand...
Click to expand...
 
Back
Top