CODEWITHSUNDEEP
sqlgreatest-n-per-group
user7415636
user7415636

Reputation:

SQL to find highest value for repeated records

I have scenario like this.

ID| NAME|   EMAIL
---|------|--------------|---
11| ROCKY|  [email protected]
99| JAMES|  [email protected]
88| ROCKY|  [email protected]
77| JAMES|  [email protected]

My expected result is

ID   NAME   EMAIL
99   ROCKY  [email protected]
88   JAMES  [email protected]

Please help

Upvotes: 0

Views: 65

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81930

Codeulike would be my first choice +1

But another Option is using the WITH TIES clause

Declare @YourTable table (ID int,NAME varchar(50),EMAIL varchar(50))
Insert Into @YourTable values
(11,'ROCKY','[email protected]'),
(99,'JAMES','[email protected]'),
(88,'ROCKY','[email protected]'),
(77,'JAMES','[email protected]')

Select Top 1 with Ties *
 From  @YourTable
 Order By Row_Number() over (Partition By Name,EMail Order by ID Desc)

Returns

ID  NAME    EMAIL
99  JAMES   [email protected]
88  ROCKY   [email protected]

Upvotes: 0

Guillaume CR
Guillaume CR

Reputation: 3016

Assuming you are using MSSQL and your expected result is the exact opposite of what you posted, you'll want to use the GROUP BY clause:

SELECT MAX(ID), NAME, EMAIL FROM YourTable GROUP BY NAME, EMAIL

Upvotes: 1

Dave Clough
Dave Clough

Reputation: 100

Since you did not provide your SQL statement I can only guess what you are trying to do.

Did you try?

ORDER BY ID desc

Upvotes: 0

codeulike
codeulike

Reputation: 23064

I think your expected result has a typo. But I think what you want is a simple group by:

select name, email, max(id)
from yourtable
group by name, email

Upvotes: 2

Related Questions