Duplicates Removing

Possible Duplicate: Delete duplicate records from a SQL table without a primary key I have data: SELECT a , b FROM ( select a = 1, b = 30

Solution 1:

SELECTmin(a) as a
        , b
    FROM 
    (
        select a =1, b =30unionallselect a =2, b =50unionallselect a =3, b =50unionallselect a =4, b =50unionallselect a =5, b =60
    ) t
GROUPBY b    
ORDERBY a

Solution 2:

In oracle I was able to do this using a group by clause, you should be able to do similar.

select min(a), bfrom (select 1a, 30bfrom dual
      union all
      select 2a, 50bfrom dual
      union all
      select 3a, 50bfrom dual
      union all
      select 4a, 50bfrom dual
      union all
      select 5a, 60bfrom dual)
group by b;

edit: looks like someone else came up with a MS sql solution, I'll leave this here for posterity though.

Solution 3:

The easiest way to do this is with a simple GROUP BY:

SELECT
      a
    , b
INTO #tmp
FROM

(
    select a =1, b =30unionallselect a =2, b =50unionallselect a =3, b =50unionallselect a =4, b =50unionallselect a =5, b =60
) t


SELECTDISTINCTMIN(a) AS a,b
FROM #tmp
GROUPBY b
ORDERBY a