How to Pull unique row from the table using

[mikesql710]

Hi Guys,
I am new here, Below is sample code. As an example from below, I want one unique records (id,client_id) where amount should be higher.
cREATE TABLE ##TEMP(
ID INT,
Client_ID int
,amount decimal(10,2)
)
insert into ##TEMP
select 1,234,0.00
union
select 1,856,100.00
union
select 1,924,400.00
union
select 2,234,0.00
union
select 2,856,0.00
union
select 2,924,0.00

select * from ##TEMP

Here is final result I am looking
ID,Client_ID,Amount
1,924,400.00
2,234,0.00

I want unique Row by ID, With the highest Rate, If Rates are same or 0.00 pick the lowest client_id

Thank You.

[skhanal]

Use GROUP BY on client_id and max rate. Then match that with table again on client_id and rate to get id.

It can be done is a query with subquery.





-

[swethamandala]

select id, Client_ID, amount
from
(
select id, Client_ID, amount, ROW_NUMBER() over(partition by id order by amount desc,client_id asc ) as rnk
from ##TEMP
) t
where rnk = 1
Capture.JPG