Remove dulicate rows using SQL

Question

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 | +------------+-----------+--------+

Try to use SELECT DISTINCT and MAX() referred to prioriti field. — Francesco Irrera
– Francesco Irrera, Commented May 13, 2015 at 12:55
I Did. but select distinct will only remove duplicates which contains same values in all the columns. In my table the employee id 1234 contains two different values in the priority column hence it will not be considered as duplicate rows. — gaayathiri srinivasan
– gaayathiri srinivasan, Commented May 13, 2015 at 12:57

Tom · Accepted Answer · 2015-05-13 12:56:59Z

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)

Thank you so much for the help. I have tried using rank over partition and that helped.

JuanZe · Accepted Answer · 2015-05-13 15:20:00Z

0

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

edited May 13, 2015 at 15:20

JuanZe

8,17746 silver badges58 bronze badges

answered May 13, 2015 at 15:06

user4896373

262 bronze badges

1 Comment

gaayathiri srinivasan Over a year ago

Thanks. Have used the similar logic and it worked :)

Collectives™ on Stack Overflow

Remove dulicate rows using SQL

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related