CODEWITHSUNDEEP
sql
gaayathiri srinivasan
gaayathiri srinivasan

Reputation: 1

Remove dulicate rows using SQL

I want to know if there is a way to remove duplicate values from a table. The key 'distinct' will fetch us the unique rows however if one value differs in a column, it wont. so just wanted to know if this can be achieved by any means. Hope the below example will help.

For example : In the below table there are two entries for Emp_ID 1234 with two different priorities. my output should consider the higher priority row alone. Is it possible? 

My table
+---------+------+--------+-------+
| Employee_ID| priority  | gender |
+------------+-----------+--------+
| 1234       |   1       | F      |
| 1234       |   10      | F      |
| 5678       |    2      | M      |
| 5678       |   25      | M      |
| 9101       |   45      | F      |
+------------+-----------+--------+

Output

+---------+------+--------+-------+
| Employee_ID| priority  | gender |
+------------+-----------+--------+
| 1234       |    1      | F      |
| 5678       |    2      | M      |
| 9101       |   45      | F      |
+------------+-----------+--------+

Upvotes: 0

Views: 49

Answers (2)

user4896373
user4896373

Reputation: 26

select Employee_ID,max(priority) as priority,gender
from table
group by Employee_ID,gender

Upvotes: 0

Tom
Tom

Reputation: 747

DELETE
FROM Table t
WHERE EXISTS ( SELECT Employee_ID FROM Table WHERE Employee_ID = t.Employee_ID AND priority < t.Priority)

That is if you really want to remove them from the table. The Exists part can also be used in a select query to leave the values in the Original table.

SELECT *
FROM Table t
WHERE NOT EXISTS (SELECT Employee_ID FROM Table WHERE Employee_ID = t.Employee_ID AND priority > t.Priority)

Upvotes: 2

Related Questions