1

I'm trying to create a query that will pull the first 10 records of each id in a table.

Something like this sounds: 

select distinct top 2 id, column1 from table group by id, column1


ID        Column1
1         ab
1         ac
1         ad
2         df
2         gf
2         dfdf
2         hgf
3         wa
3         hgh
3         dfgg
4         fgfgg

So the above table would return the first two results for each ID like this:

ID        Column1
1         ab
1         ac
2         df
2         gf
3         wa
3         hgh
4         fgfgg
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mikecancook
  • 761
  • 2
  • 13
  • 21
  • How do you define "first 10"? How do you determine the sequence when the ID is the same? – bobs Jul 29 '10 at 16:22

1 Answers1

3

ROW_NUMBER() is very useful for this type of thing.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

SELECT * FROM (
SELECT 
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) as RowNum, 
    ID, 
    Column1
FROM
    Table
) MyData
WHERE RowNum < 10
Ian Jacobs
  • 5,456
  • 1
  • 23
  • 38