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.