SQL - filter duplicate rows based on a value in a different column

Question

I have an SQL table:

+-------------+-----------+---------+
|     ID      |  position |  user   |
+-------------+-----------+---------+
|      1      |     1     |     0   |
|      2      |     2     |     0   |
|      3      |     3     |     0   |
|      4      |     4     |     0   |
|      5      |     5     |     0   |
|      6      |     6     |     0   |
|      7      |     7     |     0   |
|      8      |     7     |     1   |
+-------------+-----------+---------+

I would like to filter the duplicate row based on position column and the distinct value of user column, for the first query I need to have the following result:

+-------------+-----------+---------+
|     ID      |  position |  user   |
+-------------+-----------+---------+
|      1      |     1     |     0   |
|      2      |     2     |     0   |
|      3      |     3     |     0   |
|      4      |     4     |     0   |
|      5      |     5     |     0   |
|      6      |     6     |     0   |
|      8      |     7     |     1   |
+-------------+-----------+---------+

For the second query I need the following:

+-------------+-----------+---------+
|     ID      |  position |  user   |
+-------------+-----------+---------+
|      1      |     1     |     0   |
|      2      |     2     |     0   |
|      3      |     3     |     0   |
|      4      |     4     |     0   |
|      5      |     5     |     0   |
|      6      |     6     |     0   |
|      7      |     7     |     0   |
+-------------+-----------+---------+

What queries do I need to achieve this?

Thanks.

what IF it took another value (2 for example)? in this case what will you need? — Zeina
– Zeina, Commented Jan 23, 2017 at 7:33

Tim Biegeleisen · Accepted Answer · 2017-01-23 07:10:47Z

1

In the absence of further information, the two queries below assume that you want to resolve duplicate positions by taking either the larger (maximum) user value, in the first case, or the smaller (minimum) user value in the second case.

First query:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT position, MAX(user) AS max_user
    FROM yourTable
    GROUP BY position
) t2
    ON t1.position = t2.position AND
       t1.user     = t2.max_user

Second query:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT position, MIN(user) AS min_user
    FROM yourTable
    GROUP BY position
) t2
    ON t1.position = t2.position AND
       t1.user     = t2.min_user

answered Jan 23, 2017 at 7:10

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Shimi Bitton Over a year ago

Thank you, that's exactly what I was looking for.

Collectives™ on Stack Overflow

SQL - filter duplicate rows based on a value in a different column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related