keep only one duplicate value in SQL-CodePudding

I need to delete duplicate rows based on two columns in SQL:

FROM

ID	TEXT
A	hola
A	hello
B	arigato
B	adios

ID	TEXT
A	hola
B	arigato

In a nutshell, remove ID duplicates and keep any of their TEXT strings.

The next SQL script didn't work:

SELECT DISTINCT *
FROM table_name

Thanks for your help.

CodePudding user response：

Using rank is a possible solution for your use-case.

select ID, TEXT
from (
    select *,
           rank() over(partition by ID order by TEXT) as rnum
    from table_name
) a
where rnum = 1

Depending on the database you are using (postgresql, mysql, mssql, etc.), you could alternatively use row_number() to do a similar ranking and selecting of rows.

select ID, TEXT
from (
    select *,
           row_number() over(partition by ID order by TEXT) as rnum
    from table_name
) a
where rnum = 1

CodePudding user response：

Use GROUP BY. here is some doc about GROUP BY

SELECT * FROM table_name
GROUP BY id