How to select unique records by SQL

http://stackoverflow.com/questions/1641718/how-to-select-unique-records-by-sql

8 down vote favorite 4

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. sql share | improve this question edited  Oct 29 '09 at 5:12 OMG Ponies 108k 13 110 198 asked  Oct 29 '09 at 5:06 Yinan 361 1 2 15 76% accept rate

feedback

4 Answers

active oldest votes

up vote 17 down vote accepted	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 share | improve this answer answered  Oct 29 '09 at 5:09 marshall 2,471 14 30
	feedback

up vote 9 down vote	If you only need to remove duplicates then use DISTINCT. GROUP BY should be used to apply aggregate operators to each group GROUP BY v DISTINCT share | improve this answer answered  Oct 29 '09 at 5:10 rahul 57.3k 10 83 125
	feedback

up vote 1 down vote	It depends on which rown you want to return for each unique item. Your data seems to indicate the minimum data value so in this instance for SQL Server. SELECT item, min(data) FROM table GROUP BY item share | improve this answer answered  Oct 29 '09 at 5:15 Dave Barker 2,275 5 15
	feedback

up vote 0 down vote

I find that if I can't use DISTINCT for any reason, then GROUP BY will work. share | improve this answer answered  Feb 28 '11 at 9:58 John Hamelink 120 1 9

8 down vote favorite 4

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. sql share | improve this question edited  Oct 29 '09 at 5:12 OMG Ponies 108k 13 110 198 asked  Oct 29 '09 at 5:06 Yinan 361 1 2 15 76% accept rate

feedback

4 Answers

active oldest votes

up vote 17 down vote accepted	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 share | improve this answer answered  Oct 29 '09 at 5:09 marshall 2,471 14 30
	feedback

up vote 9 down vote	If you only need to remove duplicates then use DISTINCT. GROUP BY should be used to apply aggregate operators to each group GROUP BY v DISTINCT share | improve this answer answered  Oct 29 '09 at 5:10 rahul 57.3k 10 83 125
	feedback

up vote 1 down vote	It depends on which rown you want to return for each unique item. Your data seems to indicate the minimum data value so in this instance for SQL Server. SELECT item, min(data) FROM table GROUP BY item share | improve this answer answered  Oct 29 '09 at 5:15 Dave Barker 2,275 5 15
	feedback

up vote 0 down vote

I find that if I can't use DISTINCT for any reason, then GROUP BY will work. share | improve this answer answered  Feb 28 '11 at 9:58 John Hamelink 120 1 9