CODEWITHSUNDEEP
mysqlsqlsql-server
somputer
somputer

Reputation: 245

SQL Server : how to avoid duplicate data?

enter image description here

I want to query above picture.

Left picture is original data, right picture is query data.

select distinct ID, Nickname, Revision 
from test_table

This query do not show above picture.

How to avoid duplicate data?

Upvotes: 7

Views: 627

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

If SQL Server, using window function ROW_NUMBER in subquery:

select t.id, t.nickname, t.revision
from (
    select t.*, row_number() over (
            partition by t.id order by t.revision desc
            ) rn
    from your_table t
    ) t
where rn = 1;

Or using TOP with ties with ROW_NUMBER:

select top 1 with ties *
from your_table
order by row_number() over (
        partition by id order by revision desc
        )

If MySQL: 

select t.*
from your_table t
inner join (
    select id, MAX(revision) revision
    from your_table
    group by id
    ) t1 on t.id = t1.id
    and t.revision = t1.revision;

Upvotes: 15

Jamie
Jamie

Reputation: 11

select distinct ID, Nickname, MAX(Revision) 
from test_table 
group by ID

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93714

Another trick using TOP 1 with TIES 

SELECT Top 1 with ties *
    FROM your_table t
Order by row_number() over (partition BY t.id order by t.revision DESC)

Upvotes: 5

Related Questions