CODEWITHSUNDEEP
sqlsql-server
FirstAider
FirstAider

Reputation: 77

Return top 1 record based on a condition in SQL Server

I have the following table and records CUST_ITEMS_TBL

How do I return all record and top 1 record where CUSTID appears more than once.

The result should like like

CUSTID  ORDERID  ORDERDATE ITEMPRICE
50         1      5/10/2019   10
150        2      5/10/2019   11
51         3      5/10/2019   50

Distinct CUSTID isn't working as I need to display all the columns. Appreciate any help.

Upvotes: 0

Views: 46

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

it seems you need row_number()

select a.* from 
(select *,row_number()over(partition by custid order by itemprice desc)rn
from table_name
) a where a.rn=1

Upvotes: 1

Related Questions