When I perform SELECT * FROM table I got results like below:
1 item1 data1
2 item1 data2
3 item2 data3
4 item3 data4
As you can see, there are dup records from column2 (item1 are dupped). So how could I just get result like this:
1 item1 data1
2 item2 data3
3 item3 data4
Only one record are returned from the duplicate, along with the rest of the unique records.
9 Answers
With the distinct keyword with single and multiple column names, you get distinct records:
SELECT DISTINCT column 1, column 2, ...
FROM table_name;
There are 4 methods you can use:
- DISTINCT
- GROUP BY
- Subquery
- Common Table Expression (CTE) with ROW_NUMBER()
Consider the following sample TABLE with test data:
/** Create test table */
CREATE TEMPORARY TABLE dupes(word text, num int, id int);
/** Add test data with duplicates */
INSERT INTO dupes(word, num, id)
VALUES ('aaa', 100, 1)
,('bbb', 200, 2)
,('ccc', 300, 3)
,('bbb', 400, 4)
,('bbb', 200, 5) -- duplicate
,('ccc', 300, 6) -- duplicate
,('ddd', 400, 7)
,('bbb', 400, 8) -- duplicate
,('aaa', 100, 9) -- duplicate
,('ccc', 300, 10); -- duplicate
Option 1: SELECT DISTINCT
This is the most simple and straight forward, but also the most limited way:
SELECT DISTINCT word, num
FROM dupes
ORDER BY word, num;
/*
word|num|
----|---|
aaa |100|
bbb |200|
bbb |400|
ccc |300|
ddd |400|
*/
