Removing duplicates from SQL query

Question

I'm trying to create a SQL query that will not return rows that have the same ID. I would also like to specify a primary row so that, in the event two rows have the same ID, the primary row will be returned. If no primary row is specified I would like to return the first row.

Here is an example of the database I would like to query.

+----+---------+-------+
| id | primary | label |
+----+---------+-------+
| 1  |    Y    |   A   |
| 1  |         |   B   |  
| 2  |         |   C   |
| 2  |         |   D   |
| 3  |         |   E   |
+----+---------+-------+

Here is an example of the result I am trying to achieve

+----+---------+-------+
| id | primary | label |
+----+---------+-------+
| 1  |    Y    |   A   |
| 2  |         |   C   |
| 3  |         |   E   |
+----+---------+-------+

I've been trying to use select distinct but I'm very unsure as to the direction to pursue to solve this problem. Any help is greatly appreciated. Thank you!

What do you mean by "the first row"? The first according to the label alphabetically? If so, is the label always distinct? — Joachim Isaksson
– Joachim Isaksson, Commented May 14, 2014 at 22:02
I meant the first row as it exists in the database. The labels are just a way to distinguish the rows so it can be more easily noticed that rows with like ids only appear once in the result. — Jacthoma
– Jacthoma, Commented May 14, 2014 at 22:05
Without ordering by something "the first row as it exists in the database" is pseudo random depending on indexes/caching etc. It won't keep track of insertion order by itself. — Joachim Isaksson
– Joachim Isaksson, Commented May 14, 2014 at 22:08
Row order "as it exists in the database" shouldn't be taken into account because that can change depending on DB versions etc. In short - that's unstable solution :) Better order by label. — DarkSide
– DarkSide, Commented May 14, 2014 at 22:08
In my case it doesn't matter which one I take if they have duplicate ids and no primary has been specified but I'll definitely keep that in mind for the future. Thank you for the feedback! — Jacthoma
– Jacthoma, Commented May 14, 2014 at 22:27

Brian DeMilia · Accepted Answer · 2014-05-14 22:10:52Z

2

Subqueries would be more appropriate than DISTINCT in your case.

Try the below. Here is a demonstration of it getting your desired result: http://sqlfiddle.com/#!2/97fdd3/1/0

By the way, when there is no "primary" for the ID, this will choose the lowest label value for that ID. This is as others have stated more reliable than the 'order in the database'.

select *
  from tbl t
 where t.label = (select x.label
                    from tbl x
                   where x.primary = 'Y'
                     and x.id = t.id)
    or (not exists
        (select 1
           from tbl x
          where x.primary = 'Y'
            and x.id = t.id) and
        t.label = (select min(x.label) from tbl x where x.id = t.id))

answered May 14, 2014 at 22:10

Brian DeMilia

13.3k1 gold badge25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2014-05-14 22:28:26Z

1

I can see you already got an answer, but you could also use a regular LEFT JOIN;

SELECT * FROM mytable a 
LEFT JOIN mytable b
  ON a.id = b.id AND (
            a.label>b.label AND a.primary IS NULL AND b.primary IS NULL OR 
                                a.primary IS NULL AND b.primary='Y')
WHERE b.id IS NULL

An SQLfiddle to test with.

answered May 14, 2014 at 22:28

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

1 Comment

Jacthoma Over a year ago

Thank you for your response! It's good for my SQL learning to see multiople methods of accomplishing tasks

Barmar · Accepted Answer · 2014-05-14 22:07:52Z

0

SELECT a.id, 
       IFNULL(primaries.primary, '') AS primary, 
       IFNULL(primaries.label, secondary.label) AS label
FROM yourTable AS a
LEFT JOIN (SELECT *
           FROM yourTable
           WHERE primary = 'Y') AS primaries
ON a.id = primaries.id AND a.label = primaries.label
LEFT JOIN (SELECT id, MIN(label) AS label
           FROM yourTable
           WHERE primary != 'Y'
           GROUP BY id) AS secondary
ON a.id = secondary.id AND a.label = secondary.label

answered May 14, 2014 at 22:07

Barmar

789k57 gold badges555 silver badges669 bronze badges

Comments

DarkSide · Accepted Answer · 2014-05-14 22:09:02Z

0

Maybe something like this works (not tested):

SELECT DISTINCT
      t1.id
     ,t2.primary
     ,t2.label
FROM table t1
    LEFT JOIN (
        SELECT id, primary, label
        FROM table
        GROUP BY id
        ORDER BY primary DESC, label ASC
        LIMIT 1
    ) t2 ON t2.id=t1.id

answered May 14, 2014 at 22:09

DarkSide

3,7191 gold badge28 silver badges36 bronze badges

1 Comment

Barmar Over a year ago

Your subquery just returns one row, not one row for each id.

Collectives™ on Stack Overflow

Removing duplicates from SQL query

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related