How to Find a Higher Duplicate ID in SQL Server?

Question

I need a help with a specific scenario I ran into.

I have a table structure like this.

In this table(lets call it a user table) ID is the primary key. I want to find the higher value of the ID if the data is duplicated in the username column.

For an example, John is listed 4 times and the highest ID of that data is 10. So i need that in my result.

I can find the duplicate but max id I am having some issue.

Answer 1

Using MAX Function:

Lists both duplicated rows and non duplicated also

 Select Max(ID), username from table group by username

If u need only duplicated only then

 Select Max(ID), username from table group by username
 Having min(ID)<>Max(id)