Results 1 to 3 of 3

Thread: How to Pull unique row from the table using

  1. #1
    Join Date
    Apr 2017
    Posts
    1

    How to Pull unique row from the table using

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.





    -

  3. #3
    Join Date
    May 2014
    Posts
    1
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •