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?
-
what is the result you are getting currently?Ambrish Pathak– Ambrish Pathak2016-12-28 05:14:35 +00:00Commented Dec 28, 2016 at 5:14
-
i want to get only last revision data. and i use sql server.somputer– somputer2016-12-28 05:15:18 +00:00Commented Dec 28, 2016 at 5:15
-
@JaydipJ -- BobT is the 3rd version for user ID 1.Hogan– Hogan2016-12-28 05:25:48 +00:00Commented Dec 28, 2016 at 5:25
Add a comment
|
3 Answers
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;
Comments
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)
Comments
select distinct ID, Nickname, MAX(Revision)
from test_table
group by ID
1 Comment
Ankit Agrawal
you don't have to use both distinct and group by clause in a same statement