MYSQL - How to select rows based on distinct values of a column?

Question

My question is similar to another stackoverflow post - Select Unique Rows Based on Single Distinct Column - MySQL

but I have doubts on the answers

   +----+---------+-------------------+-------------+  
  | id | title   | email             | genre |   
     +----+---------+-------------------+-------------+    
  |  3 | test    | [email protected]   | 1         |    
  |  4 | i agree | [email protected]    | 2         |    
  |  5 | its ok  | [email protected] | 3         |   
  |  6 | hey     | [email protected]  | 4         |    
  |  7 | nice!   | [email protected] | 5         |   
  |  8 | yeah    | [email protected]  | 3         |  
  |  9 | hey     | [email protected]   | 4         |   
  | 10 | nice!   | [email protected]  | 5         |  
  | 11 | yeah    | [email protected]  | 3         |
+----+---------+-------------------+-------------+

Expectation: select rows based on unique genre

+----+---------+-------------------+-------------+
   | id | title   | email             | genre |
   +----+---------+-------------------+-------------+
   |  3 | test    | [email protected]   | 1         |
   |  4 | i agree | [email protected]    | 2         |
   |  5 | its ok  | [email protected] | 3         |
   |  6 | hey     | [email protected]  | 4         |
   |  7 | nice!   | [email protected] | 5         |  
   +----+---------+-------------------+-------------+

or

+----+---------+-------------------+-------------+
   | id | title   | email             | genre |
   +----+---------+-------------------+-------------+
   |  3 | test    | [email protected]   | 1         |
   |  4 | i agree | [email protected]    | 2         |
   |  6 | hey     | [email protected]  | 4         |
   | 10 | nice!   | [email protected]  | 5         |
   | 11 | yeah    | [email protected]  | 3         |
   +----+---------+-------------------+-------------+

or

+----+---------+-------------------+-------------+
   | id | title   | email             | genre |
   +----+---------+-------------------+-------------+
   |  3 | test    | [email protected]   | 1         |
   |  4 | i agree | [email protected]    | 2         | 
   |  8 | yeah    | [email protected]  | 3         |
   |  9 | hey     | [email protected]   | 4         |
   | 10 | nice!   | [email protected]  | 5         |
    +----+---------+-------------------+-------------+

i.e. any row with 1 , any row with 2 , any row with 3 ,...

Goal: Query should not return 2 rows with same genre.

If I use

select * from...where ... group by genre;

I get error because of nonaggregated columns

I can add ANY_VALUE(that_column), it works but do not know of any side effects.

select ...ANY_VALUE(that_column1), ...ANY_VALUE(that_column2) .... from ... where ... group by genre;

Question: I use Spring JPA , can I use this long query in my spring repository class @Query( select....) public List findData(..);

or is there an alternative efficient query ?

Thanks

but do not know of any side effects. The only possible side effect (in theory) may be that the values for different columns of one output row may be taken from different source rows... but I have never seen this in practice yet. — Akina
– Akina, Commented Feb 19, 2022 at 22:13
This is a huge (unacceptable) problem :) . so will manage in my code — Kris Swat
– Kris Swat, Commented Feb 19, 2022 at 23:34

Bill Karwin · Accepted Answer · 2022-02-20 05:03:09Z

0

Here's a solution that will pick a row randomly from the group, while ensuring all columns returned by the query are from the same row.

SELECT id, title, email, genre
FROM (
 SELECT id, title, email, genre,
   ROW_NUMBER() OVER (PARTITION BY genre ORDER BY RAND()) AS rownum
 FROM MyTable
) AS t
WHERE rownum = 1;

This solution uses window functions, which are a new feature of MySQL 8.0. If you use an older version, you must upgrade (you should do this anyway, because MySQL 5.x is soon to be past its end of life).

answered Feb 20, 2022 at 5:03

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kris Swat Over a year ago

Thanks Bill, I use cloudSQL, so probably still 5.7. I used java List to Map to find unique set. rownum = 1 means get only 1 row of each different value of genre?

Bill Karwin Over a year ago

You can check the version: SELECT VERSION(); The meaning of rownum=1 means only one row from each group, since ROW_NUMBER() returns a series of unique integers starting at 1 in each group.

Bill Karwin Over a year ago

Google CloudSQL supports MySQL 8.0 since at least Sept 2020, but I don't know if your instance has been upgraded: infoq.com/news/2020/09/google-cloud-mysql-8

Bill Karwin Over a year ago

Okay, so you won't be able to use the solution I proposed above unless you upgrade. I recommend upgrading sooner or later.

Collectives™ on Stack Overflow

MYSQL - How to select rows based on distinct values of a column?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related