sql 2 way of select unique
/** Find the higher id values of duplicates, distinct only added for clarity */ SELECT distinct d2.id FROM dupes d1 INNER JOIN dupes d2 ON d2.word=d1.word AND d2.num=d1.num WHERE d2.id > d1.id /* id| --| 5| 6| 8| 9| 10| */ /** Use the previous query in a subquery to exclude the dupliates with higher id values */ SELECT * FROM dupes WHERE id NOT IN ( SELECT d2.id FROM dupes d1 INNER JOIN dupes d2 ON d2.word=d1.word AND d2.num=d1.num WHERE d2.id > d1.id ) ORDER BY word, num; /* word|num|id| ----|---|--| aaa |100| 1| bbb |200| 2| bbb |400| 4| ccc |300| 3| ddd |400| 7| */
