CODEWITHSUNDEEP
mysqlsql
Developer
Developer

Reputation: 26173

mySql select statment to get last records of duplicate entries

ID  Name
1       A
2   A
3   B
4   C
5   C
6   D
7   F
8   F

output 1 
---------
ID     Name
2   A
5   C
8   F

output 2
--------
ID     Name
1       A
3   B
4   C
6   D
7   F

I need sql select statement to get output1 and output 2

I need fetch the last entries of duplicate records in output 1 and all rest of the entries in output2

Upvotes: 2

Views: 4231

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

select max(id) id, name
from tbl
group by name
having count(*) > 1

select tbl.*
from tbl
left join (
    select max(id) id
    from tbl
    group by name
    having count(*) > 1) x on x.id = tbl.id
where x.id is null

Upvotes: 7

Related Questions