Yinan Yinan - 3 months ago 12
SQL Question

How to select unique records by SQL

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.

Answer

You can use SELECT DISTINCT or GROUP BY to do this.

SELECT DISTINCT a, c
FROM table_c

or

SELECT a, b
FROM table_c
GROUP BY a, b

GROUP BY will be more helpful if you want to use some aggregate function like COUNT() or SUM()

SELECT a, b, count(*)
FROM table_c
GROUP BY a, b

SELECT a, b, sum(d)
FROM table_c
GROUP BY a, b