CODEWITHSUNDEEP
mysqlsql
ProgrammerGirl
ProgrammerGirl

Reputation: 3223

How to SELECT two columns, where one column must be DISTINCT?

Table1 has 3 columns: col1, col2, col3

How can I SELECT all the DISTINCT values of col1 where col3 equals a certain value, then sort it by col2 DESC, yet have the distinct col1 results show their corresponding col2 value?

I tried the following but it did not work:

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

The above does not result in distinct values of col1. If I remove ", col2", then it will show distinct values of col1, but it won't show me their corresponding col2 values.

So how do I do it?

Upvotes: 6

Views: 6408

Answers (4)

vyegorov
vyegorov

Reputation: 22905

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

EDIT: This answer have had several revisions, keeping the most accurate one.

Upvotes: -1

Quassnoi
Quassnoi

Reputation: 425763

I'm assuming you want greatest 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) for this to work fast.

Update:

If you only need these two columns, the query can indeed be made more simple:

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

But if you need the whole record (along with the additional fields possbily contained in the table), you would need the initial syntax.

Upvotes: 2

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52147

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

Upvotes: 11

Clockwork-Muse
Clockwork-Muse

Reputation: 13106

I'm pretty sure mySQL supports the GROUP BY clause, and given you want the greatest value of col2, this should get you the results you want: 

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

Upvotes: 4

Related Questions