Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

DISTINCT on multiple columns

Tags:

mysql

I have, SELECT DISTINCT (first),second,third FROM table

AND i want not only the first to be DISTINCT and the second to be DISTINCT to but the third to stay without DISTINCT , i tryed like that.

SELECT DISTINCT (first,second),third FROM table

And couple more things but didnt worked.

like image 359
weardstuff Avatar asked Apr 27 '11 13:04

weardstuff


1 Answers

SELECT  m.first, m.second, m.third -- and possibly other columns
FROM    (
        SELECT  DISTINCT  first, second
        FROM    mytable
        ) md
JOIN    mytable m
ON      m.id =
        (
        SELECT  id
        FROM    mytable mi
        WHERE   mi.first = md.first
                AND mi.second = md.second
        ORDER BY
                mi.first, mi.second, mi.third
        LIMIT 1
        )

Create an index on (first, second, third) for this to work fast.

like image 107
Quassnoi Avatar answered Oct 06 '22 17:10

Quassnoi