SQL - Restrict the number of rows returned based on a count of rows

Question

Here is what my data looks like:

item_ID | group_ID | count_of_items_in_group

2|ABC|3

5|ABC|3

9|ABC|3

29|DEF|3

3|DEF|3

4|DEF|3

200|XYZ|2

300|XYZ|2

600|GHI|1

SQL Filddle: http://sqlfiddle.com/#!2/dfe09/1

For each group, I want to limit the number of item_IDs returned to a max of 2. I do not care which 2 are returned. If the group has less than 2 rows return only the 1 row.

I can't write a select top * 2 for each group and union the selects because I have a few hundred groups.

I'm not sure where to start, your help is greatly appreciated.

Using MS SQL 2005

(Table layout is exactly as it is in the sample, it is a view based on a number of queries)

Answer 1

Use the ROW_NUMBER() function for this:

SELECT *
FROM (select *,ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY item_id) as RowRank 
      from items_in_groups
      )sub
WHERE RowRank <=2

Demo: SQL Fiddle

The ROW_NUMBER() function assigns a number to each row. PARTITION BY is optional, but used to start the numbering over for each value in that group, ie: if you PARTITION BY group_id then for each unique group_id value the numbering would start over at 1. ORDER BY of course is used to define how the counting should go, and is required in the ROW_NUMBER() function.