Duplicates removing

Question

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
        union all 
        select a = 2, b = 50
        union all 
        select a = 3, b = 50
        union all 
        select a = 4, b = 50
        union all 
        select a = 5, b = 60
    ) t

I have to get output (next (order by a) dublicate records should be excluded from result set):

a           b
----------- -----------
1           30
2           50
3           50  -- should be excluded
4           50  -- should be excluded
5           60

So the question is more complicated than `select min(a), b from ... group by b`? -- because that would work with your example data — antlersoft, Sep 16 '11 at 16:29
@antlersoft it is good enough. I have: a is int and b is uniqueidentifier, so it will work too. thank you. — garik, Sep 16 '11 at 20:11

score 4 · Accepted Answer · answered Sep 16 '11 at 16:29

4

SELECT
          min(a) as a
        , b
    FROM 
    (
        select a = 1, b = 30
        union all 
        select a = 2, b = 50
        union all 
        select a = 3, b = 50
        union all 
        select a = 4, b = 50
        union all 
        select a = 5, b = 60
    ) t
GROUP BY b    
ORDER BY a

answered Sep 16 '11 at 16:29

Mikael Eriksson

136,425
22
210
281

1

What if a had a non-numeric entry? – Pepe Sep 16 '11 at 16:31
@P.R - You should still be able to use `min()`. – Mikael Eriksson Sep 16 '11 at 16:34
it crashes and says that it wasn't able to convert to number (I tried it on MS) – Pepe Sep 16 '11 at 18:19
@P.R - what data type did you use? What did you test? You got to have the same data type for all values in a column. – Mikael Eriksson Sep 16 '11 at 18:24

score 1 · Answer 2 · answered Sep 16 '11 at 16:29

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

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

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

score 1 · Answer 3 · answered Sep 16 '11 at 16:31

1

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

SELECT
      a
    , b
INTO #tmp
FROM

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


SELECT DISTINCT MIN(a) AS a,b
FROM #tmp
GROUP BY b
ORDER BY a

answered Sep 16 '11 at 16:31

Wil

4,130
1
16
15

1

The distinct won't do anythin as you are grouping by b and usin an aggegate on a, each row would already be gaurunteed to be distinct. – TimothyAWiseman Sep 16 '11 at 16:50

Duplicates removing

3 Answers3

Linked