3

Possible Duplicate:
How do I select a fixed number of rows for each group?

For example... if I have this table (sorted by color):

--------------
| id | color |
--------------
| 95 | red   |
| 7  | red   |
| 44 | red   |
| 46 | red   |
| 49 | red   |
| 24 | green |
| 37 | green |
| 91 | green |
| 88 | green |
| 44 | blue  |
| 10 | blue  |
| 11 | blue  |
--------------

Is there a way for me to get the 2 rows from each color? Example:

--------------
| id | color |
--------------
| 7  | red   |
| 44 | red   |
| 24 | green |
| 37 | green |
| 10 | blue  |
| 11 | blue  |
--------------
Community
  • 1
  • 1
supercoolville
  • 8,636
  • 20
  • 52
  • 69

1 Answers1

9

you can have something like this,

SELECT id, color
FROM   TableName a
WHERE 
(
   SELECT COUNT(*) 
   FROM   TableName AS f
   WHERE  f.color = a.color AND f.id <= a.id
) <= 2;
John Woo
  • 258,903
  • 69
  • 498
  • 492