Menu
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?
371
The SQL DISTINCT keyword, which we have already discussed is used in conjunction with the SELECT statement to eliminate all the duplicate records and by fetching only the unique records.
If you want the query to return only unique rows, use the keyword DISTINCT after SELECT . DISTINCT can be used to fetch unique rows from one or more columns. You need to list the columns after the DISTINCT keyword.
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;
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
