CODEWITHSUNDEEP
mysql
alexk745
alexk745

Reputation: 38

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?

Upvotes: 0

Views: 45

Answers (2)

user6622043
user6622043

Reputation: 101

select * from yourtable
where column2 = (select column2 from yourtable
                 order by column2 limit 1)

find the smallest number in column2 and find records with this number

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

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

Upvotes: 2

Related Questions