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 vote337 views1 answer
Prodection ready postgres database step by step
SQL
0 vote320 views1 answer
Sqlcmd xml output insert line break after every 2033 characters
SQL
11 votes306 views2 answers
Set default column value sql
SQL
16 votes346 views6 answers
Your connection attempt failed for user 'root' to the mysql server at localhost 3306
SQL
0 vote329 views1 answer
Sql find record cannot cast date
SQL
16 votes328 views6 answers
This is incompatible with sql_mode=only_full_group_by
SQL
0 vote361 views1 answer
Mysql, how to query the table comments?
SQL
0 vote320 views1 answer
Linebreak sql
SQL
0 vote305 views1 answer
Left join in codeigniter query builder
SQL
7 votes335 views3 answers
Postgresql dump and restore db
SQL