SQL for Selecting fix number of row "families"

Question

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.

You haven't said which three rows you want returning for each id. For id 1, you have shown the first three, whereas for id 2, you have the first two and the last. What are the criteria for select the rows? — Mike, Jul 04 '10 at 13:46
I haven't said because order does not matter, it might as well be random or it might be defined using order by. How does this affect the solution? — Maxim Veksler, Jul 08 '10 at 05:58

score 0 · Answer 1 · answered Feb 11 '13 at 10:48

0

You can use:

select  top 3 * from [Table_Name] where id='1' or id='2'

answered Feb 11 '13 at 10:48

Code_Tech

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

i hope this link help :

more in the answers of this questions :

edited May 23 '17 at 12:13

Community

answered Jul 04 '10 at 12:58

Haim Evgi

score 0 · Answer 3 · edited May 23 '17 at 11:57

0

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

edited May 23 '17 at 11:57

Community

answered Jul 04 '10 at 13:03

Cade Roux

score 0 · Answer 4 · answered Jul 04 '10 at 15:16

0

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.

answered Jul 04 '10 at 15:16

Brian Hooper

4 Answers4