SQL for Selecting fix number of row "families"

Question

Hello,

Given table

| id | user | 
| 1  | 1    |
| 1  | 2    |
| 1  | 3    |
| 1  | 4    |
| 2  | 5    |
| 2  | 6    |
| 2  | 7    |
| 2  | 8    |

I want to write a query that will return 3 rows for id=1 and 3 rows for id=2 (order does not matter, yet I would assume that it can be enforced).

So the end result should be something like (Note, 3 rows for each id):

| id | user | 
| 1  | 1    |
| 1  | 2    |
| 1  | 3    |
| 2  | 5    |
| 2  | 7    |
| 2  | 8    |

How should I write this SQL? My attempts with HAVING have not brought something useful so far.

Thank you, Maxim.

Answer 1

i hope this link help :

http://blog.aharbick.com/2006/09/mysql-groupwise-limiting.html

more in the answers of this questions :

http://stackoverflow.com/questions/1153738/mysql-limit-inside-group

http://stackoverflow.com/questions/2643314/mysql-group-by-limit

Answer 2

There are several questions here on SO which cover this area.

Answer 3

I suspect that what you want is

SELECT DISTINCT *
     FROM my_table;

but that wouldn't be the answer to the question a you have stated it, which appears to want three arbitrarily selected rows if I read it correctly.