alexk745 alexk745 - 2 months ago 5
MySQL Question

Select "group" of rows with same column values(1 or more)

Suppose I have this table:

id | column1 | column2
--------+---------+----------
1 | 3 | 1
2 | 5 | 1
3 | 6 | 2
4 | 5 | 2
5 | 1 | 3
6 | 7 | 3
7 | 7 | 3
8 | 2 | 3
9 | 5 | 3
10 | 7 | 4


(column2 is ordered for convenience, the rows are not in this order in the actual table)

How can I select a "group" of rows with the same value in column 2? It shouldn't necessarily return more than 1. Random would be fine but I would also prefer if I could sort it by some other column too.

If I am not making myself clear, this is what I want it to return:

id | column1 | column2
--------+---------+----------
1 | 3 | 1
2 | 5 | 1


or

id | column1 | column2
--------+---------+----------
3 | 6 | 2
4 | 5 | 2


or

id | column1 | column2
--------+---------+----------
10 | 7 | 4


or... (etc)

So first it should return all the rows with the value 1 in column 2, then if I delete them it should return all the rows with the value 2 in column 2, etc.

Note that column2 is integer for the sake of simplicity and also I don't want to provide a specific value and column2 can have any value. The only way I can think of to make this work is with two queries:

First
SELECT column2 FROM test_table GROUP BY column2
, to get all the distinct column2 values and the do
SELECT * FROM test_table WHERE column2=value
for each column2 value.

Is there a more efficient/short/single query way to achieve the same result?

Answer

If I'm understanding your question correctly, here's one option using a subquery with min:

select t.*
from test_table t join (
    select min(column2) column2
    from test_table
    ) t2 on t.column2 = t2.column2
Comments