Drop duplicate rows based on criteria

Transact-SQL
,
1

I have a table that has duplicate data. Similar to below table. I want to drop duplicates keeping only the rows with value close to zero in column2
table1
Cloumn1 column2
1100 -27
1100 0
1100 10
1300 -2
1300 12

Resultant table looks like
Cloumn1 column2
1100 0
1300 -2

CREATE TABLE [TEST].[dbo].[tb1]
([Col1] FLOAT, [Col2] INT)

INSERT INTO [TEST].[dbo].[tb1] VALUES
(1100,-27),
(1100,0),
(1100,10),
(1300,-2),
(1300,12)

Hope some one can help on this. Thanks

2

Hi
What you can do is this :

  • FIRST always work with a select to check if the results are what you want :slight_smile:
    SELECT column1, column2
    FROM test
    WHERE column2 between 2 and -2
    GROUP BY
    column1, column2

if the results are what you expect than you can delete

DELETE * FROM TEST
WHERE column2 between 2 and -2

Ok for you ?
Cheers

3

Thanks for your reply.
Actually i'm looking for a code to findout column 2 closest figures to zero & keep only such row with closest figure.
Because my actual table has many figures.

SELECT Col1, Col2
FROM [TEST].[dbo].[tb1]
WHERE Col2 between -2 and 2
GROUP BY
Col1, Col2

I hope there should be a way. Thanks again for your reply.

4

Select col1,col2 from (
Select Col1
,col2
,row_number() over (partition by col1 order by ABS(col2)) as rw
from [TEST].[dbo].[tb1] ) abc
where rw=1

This can also be a solution

1 Like
5

Thank you very much. It works well.

6

:slight_smile: