从具有重复条目的表中获取记录

Question

I have my table structure like below in desc order

sr  group   name    status
4   class11 ghanshyam   Joined
3   class11 ghanshyam   Removed
2   class11 ghanshyam   suspended
1   class11 ghanshyam   joined

Now I want to fetch all record in group class11 whose status is Removed.

So my query will be

select * from table where group = 'class11' and status='Removed' group by name

but now ghanshyam has rejoined group after status removed and we are duplicating entries so it should not count in Removed, same for suspended account.

So how Can I fetch desired row from table

Answer 1

SELECT t1.* from [table] t1

INNER JOIN

( SELECT MAX(sr) AS nsr ,[group] ,[name] FROM [table] GROUP BY [group] ,[name] ) t2

ON t2.nsr = t1.sr
AND t1.status='Removed'