存储架构

Remove duplicate rows by using SQL

微信扫一扫,分享到朋友圈

Remove duplicate rows by using SQL
0

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

阅读原文...


Hello, buddy!

民航局:恢复波音737MAX8运行需把握三个原则

上一篇

新型健身机构24KiCK获近千万元Pre-A轮融资,联想之星及发现创投参投

下一篇

您也可能喜欢

评论已经被关闭。

插入图片
Remove duplicate rows by using SQL

长按储存图像,分享给朋友