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|
*/

Source: stackoverflow.com

Sql 2 way of select unique

WITH CTE AS (
    SELECT  *
           ,row_number() OVER(PARTITION BY word, num ORDER BY id) AS row_num
    FROM    dupes
)
SELECT  word, num, id 
FROM    cte
WHERE   row_num = 1
ORDER BY word, num;

/*
word|num|id|
----|---|--|
aaa |100| 1|
bbb |200| 2|
bbb |400| 4|
ccc |300| 3|
ddd |400| 7|
*/

Source: stackoverflow.com

Similar codes

0 vote286 views1 answer
Sql database connectivity
SQL
5 votes288 views1 answer
Mysql change primary key
SQL
0 vote288 views1 answer
How to set all the min and sec data to zero in sql server
SQL
0 vote301 views1 answer
How to get data from 2 database
SQL
-1 vote276 views1 answer
Raise ImproperlyConfigured('mysqlclient 1.3.13 or newer is required; you have %s.' % Database.__version__) django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.13 or newer is required; you have 0.9.3.
SQL
4 votes312 views4 answers
Date less than in sql
SQL
0 vote284 views1 answer
Inner joint
SQL
1 vote331 views1 answer
Floor function in sql oracle
SQL
6 votes338 views1 answer
Oracle trigger after logon on database
SQL
2 votes290 views1 answer
Drop table purge
SQL