How to select unique records by SQL

Question

When I perform "SELECT * FROM table" I got results like below:

1 item1 data12 item1 data23 item2 data34 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 data12 item2 data33 item3 data4

Only one record are returned from the duplicate, along with the rest of the unique records.

marshall 2,4711430 · Accepted Answer · 2009-10-29 05:09:53Z

you can use select distinct or group by to do this.

select distinct a, cfrom table_c

or

select a, bfrom table_cgroup 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_cgroup by a, bselect a, b, sum(d)from table_cgroup by a, b

rahul 57.3k1083125 · Answer 2 · 2009-10-29 05:10:54Z

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

Dave Barker 2,275515 · Answer 3 · 2009-10-29 05:15:24Z

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  tableGROUP BY item

John Hamelink 12019 · Answer 4 · 2011-02-28 09:58:58Z

I find that if I can't use DISTINCT for any reason, then GROUP BY will work.

marshall 2,4711430 · Accepted Answer · 2009-10-29 05:09:53Z