How to select two columns where one column should be DISTINCT?

Question

Table1 has 3 columns: col1 , col2 , col3

How to SELECT all the DISTINCT values ​​of col1 , where col3 is equal to some value, and then sort it by col2 DESC , but still the individual results of col1 show their corresponding col2 value?

I tried the following, but this did not work:

 SELECT DISTINCT (col1), col2 FROM `Table1` WHERE `col3` = 'X' ORDER BY `col2` DESC 

The above does not lead to individual col1 values. If I delete " , col2 " then it will display different col1 values, but it will not show me the corresponding col2 values.

So how do I do this?

Answer 1

Maybe something like this:

 SELECT col1, MAX(col2) col2_max FROM Table1 WHERE col3 = 'X' GROUP BY col1 ORDER BY col2_max 

?

You can play with it in this SQL Fiddle .

Answer 2

I am sure mySQL supports the GROUP BY , and if you want to get the highest col2 value, this will give you the desired results:

 SELECT col1, MAX(col2) as col2 FROM Table1 WHERE col3 = 'X' GROUP BY col1 ORDER BY col2 

Answer 3

I assume that you want the largest col2 for each col1 :

 SELECT tm.* FROM ( SELECT DISTINCT col1 FROM table1 WHERE col3 = 'X' ) t JOIN table tm ON id = ( SELECT id FROM table1 ti WHERE ti.col3 = 'X' AND ti.col1 = t.col1 ORDER BY col2 DESC LIMIT 1 ) col2 ORDER BY col2 DESC 

Create an index on (col3, col1, col2) so that it works quickly.

Update:

If you only need these two columns, the query can really be simplified:

 SELECT col1, MAX(col2) AS maxcol2 FROM table1 WHERE col3 = 'X' GROUP BY col1 ORDER BY maxcol2 DESC 

But if you need the entire record (along with the additional fields contained in the table), you will need the original syntax.

Answer 4

 SELECT t.col1, max(t.col2) FROM Table1 t WHERE t.col3='X' GROUP BY t.col1 ORDER BY t.col2 DESC; 

EDIT: There were several corrections in this answer that retained the most accurate result.