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

Answer 1

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).