CODEWITHSUNDEEP
mysqlsql
code
code

Reputation: 5642

How to obtain all rows in MySQL table where a column's value is the same?

I am a SQL novice here. Suppose we have the following table:

id              type
1               A   
2               A
3               B
4               B
5               C
6               C
7               D
8               D
...

Let's say that I do not know the possible values under the column 'type' and assume that it can be one of N number of possible values. In MySQL, how can I form a query to obtain all rows for a single type (either randomly select one type or take the first alphabetically sorted type)?

Desired output:

id              type
1               A   
2               A

or

id              type
5               C   
6               C

or

id              type
7               D   
8               D

Upvotes: 0

Views: 48

Answers (3)

Popeye
Popeye

Reputation: 35930

If you want to get first type sorted alphabatically then use min(or use max for last type) as follows:

select t.*
  from your_table t
 where t.type = (select min(tt.type) from your_table tt);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522635

You could use RANK() here, for a MySQL 8+ solution:

WITH cte AS (
    SELECT *, RANK() OVER (ORDER BY type) rnk
    FROM yourTable
)

SELECT id, type
FROM cte
WHERE rnk = 1;

This approach would return all records for the alphabetically lowest type value.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270883

You can choose a random type and get all the rows using:

select t.*
from t
where t.type = (select t2.type from t t2 order by rand() limit 1);

Upvotes: 2

Related Questions