sql 2 way of select unique

1WITH CTE AS (
2    SELECT  *
3           ,row_number() OVER(PARTITION BY word, num ORDER BY id) AS row_num
4    FROM    dupes
5)
6SELECT  word, num, id 
7FROM    cte
8WHERE   row_num = 1
9ORDER BY word, num;
10
11/*
12word|num|id|
13----|---|--|
14aaa |100| 1|
15bbb |200| 2|
16bbb |400| 4|
17ccc |300| 3|
18ddd |400| 7|
19*/
20

1/** Find the higher id values of duplicates, distinct only added for clarity */
2    SELECT  distinct d2.id
3    FROM    dupes d1
4        INNER JOIN dupes d2 ON d2.word=d1.word AND d2.num=d1.num
5    WHERE d2.id > d1.id
6
7/*
8id|
9--|
10 5|
11 6|
12 8|
13 9|
1410|
15*/
16
17/** Use the previous query in a subquery to exclude the dupliates with higher id values */
18SELECT  *
19FROM    dupes
20WHERE   id NOT IN (
21    SELECT  d2.id
22    FROM    dupes d1
23        INNER JOIN dupes d2 ON d2.word=d1.word AND d2.num=d1.num
24    WHERE d2.id > d1.id
25)
26ORDER BY word, num;
27
28/*
29word|num|id|
30----|---|--|
31aaa |100| 1|
32bbb |200| 2|
33bbb |400| 4|
34ccc |300| 3|
35ddd |400| 7|
36*/
37