How to Pull unique row from the table using
Results 1 to 2 of 2

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,897
    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.





    -

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
  •