MSSQL Delete values from table

I have a table that has 2 columns, one is column_ID and the other column_value. With this query :
(Select column_value FROM table1 group by column_value having COUNT(*)>1)) ,
I find the duplicated column_values and I want to delete the row where the Column_ID is with lower value for that row.

F.eks.
Column_ID    Column_Value
1                           22
2                           22
3                           30
4                           30

In this case I would like to delete the rows with column_ID equal to 1 and 3

How can I do that?
gosi75Business Intelligence ConsultantAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I thin that will delete all rows.


Please try this:

DELETE FROM table1
FROM table1
INNER JOIN (
    SELECT column_value, MAX(column_ID) AS max_column_id
    FROM table1
    GROUP BY column_value
    HAVING COUNT(*) > 1
) AS dups ON
    table1.column_value = dups.column_value AND
    table1.column_id < dups.max_column_id
0
 
CrashmanCommented:
Delete
x
from table1 as x inner join
(Select ID, column_value FROM table1 group by column_value having COUNT(*)> 1) as y on x.ID = y.ID
0
 
gosi75Business Intelligence ConsultantAuthor Commented:
Great, thanks a lot mate :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.