I have the following table in SQL:
I want to remove rows with the same id value.
Which query in SQL should I use?
The table should be as follows after the delete operation:
-
What is your rule for deciding which row to delete?NickW– NickW2023-05-03 20:17:59 +00:00Commented May 3, 2023 at 20:17
-
it doesn't matter. i need just oneuser– user2023-05-03 20:19:00 +00:00Commented May 3, 2023 at 20:19
-
What rdbms are you using, MS-SqlServer?Tim Schmelter– Tim Schmelter2023-05-03 21:45:43 +00:00Commented May 3, 2023 at 21:45
Add a comment
|
4 Answers
You could select all unique ids by using group by:
Select
max(a)
,id
from Table
group by id
If the above result is the result that you want to keep in your table then you could just do that:
delete FROM Table
where a not in (
Select max(a) from Table
group by id
)
Comments
Use Distinct keyword
SELECT DISTINCT a, id, c FROM table_name;
The DISTINCT keyword ensures that only unique rows are returned in the query result.
or use group by and having clause
SELECT a,id,c, COUNT(*) AS CNT FROM [SampleDB].[dbo].[Employee]
GROUP BY a,id,c HAVING COUNT(*) > 1;
==============================
DELETE FROM [SampleDB].[dbo].[Employee] WHERE a NOT IN(SELECT MAX(a) AS MaxRecordID FROM [SampleDB].[dbo].[Employee] GROUP BY id,c);
explanation is given on below source.If it donot work read the source and change accordingly
Source:https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/
2 Comments
user
thanks for your answer!' but how i delete the rows?
Paranoid0X0x0
@user edited for deleing
Let's say the table is called t, maybe a way to remove duplicates can be the use the distinct, on that column, so in your case
SELECT DISTINCT id, a, c FROM t;
Bye!
Comments
If you use SQL Server, I'd prefer a common table expression with an Over-clause, since it makes the SQL query readable and maintainable:
WITH CTE AS
(
SELECT
[a], [id], [c],
RN = ROW_NUMBER() OVER (PARTITION BY id ORDER BY a)
FROM dbo.TableName
)
DELETE FROM CTE
WHERE RN > 1
You could for example easily change it to a select see what you'll delete:
WITH CTE AS
(
SELECT
[a], [id], [c],
RN = ROW_NUMBER() OVER (PARTITION BY id ORDER BY a)
FROM dbo.TableName
)
SELECT *
FROM CTE
WHERE RN > 1
You can also easily control what you'll keep, in this case I keep the first duplicate ordered by a.
