mysql select only X number of diffent Y row values

Question

I have a table that I'm selecting from in which I only want results for 2 differt column values... Here is what I mean data wise...

some_table
+----+----------+-------------+
| id |  some_id | some_column |     
+----+----------+-------------+
|  1 |       10 |       alpha |
|  2 |       10 |       alpha |
|  3 |       10 |       alpha |
|  4 |       20 |       alpha |
|  5 |       30 |       alpha |
+----+----------+-------------+

An example of the type of query I'm running is:

SELECT * FROM some_table WHERE some_column = `alpha`;

How do I modify that select so that it only gives me results for up to 2 diffent some_id's... an example result is:

some_table
+----+----------+-------------+
| id |  some_id | some_column |     
+----+----------+-------------+
|  1 |       10 |       alpha |
|  2 |       10 |       alpha |
|  3 |       10 |       alpha |
|  4 |       20 |       alpha |
+----+----------+-------------+

It would not include id = 5 row because we only grab results for up to 2 different some_id's (10, 20 in this case).

Yes, i'm hoping to limit by the different number of some_id's. I want up to 2 different some_id's as stated in the question. — Joey Cadle, Oct 02 '13 at 12:40
This seems like a variation of the TOP N PER GROUP, which is non-trivial but written about: http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group which points to here http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group, — AgRizzo, Oct 02 '13 at 12:40

Kickstart · Answer 1 · 2013-10-02T13:26:12.933

0

Possibly use a subselect to get the first 2 ids, and then inner join that against your table

SELECT  a.id, a.some_id, a.some_column
FROM some_table a
INNER JOIN
(
    SELECT DISTINCT some_id
    FROM some_table
    ORDER BY some_id
    LIMIT 2
) b
ON a.some_id = b.some_id

edited Oct 02 '13 at 13:26

answered Oct 02 '13 at 13:16

Kickstart

21,403
2
21
33

score 0 · Answer 2 · answered Oct 02 '13 at 13:23

0

Actually figured it out on my own, just needed to use a JOIN / SELECT DISTINCT combo. Here is the correct query...

SELECT * FROM some_table s1 JOIN (SELECT DISTINCT some_id FROM s1 LIMIT 2) s2 ON s1.some_id = s2.some_id;

answered Oct 02 '13 at 13:23

Joey Cadle

188
1
3
11

score 0 · Answer 3 · answered Oct 02 '13 at 13:53

I am not sure if this is the optimal solution but it should do the trick:

SET @firstId:=(select distinct some_id from some_table limit 1) ;
SET @secondId:=(select distinct some_id from some_table limit 1,1) ;
SELECT  * 
FROM some_table
WHERE some_column="alpha"
AND some_id IN (@firstId, @secondId);

mysql select only X number of diffent Y row values

3 Answers3