locked
Distinct on one column, but display the second record of the second column RRS feed

  • Question

  • I would like to distinct on the first column

    Table :

    ID   Value

    1    test1
    2    test2
    2    test3
    3    test4

    The output must be

    1    test1
    2    test3
    3    test4


    • Edited by Lio1972 Friday, March 18, 2016 3:53 PM
    Friday, March 18, 2016 3:42 PM

Answers

  • A simple GROUP BY with MAX:

    Select id, Max(Value) as Value

    FROM yourtable

    GROUP by id

    • Proposed as answer by Naomi N Friday, March 18, 2016 4:18 PM
    • Marked as answer by Lio1972 Friday, March 18, 2016 5:31 PM
    Friday, March 18, 2016 3:55 PM

All replies

  • Hi,

    you can try this :

    ;with cte(id, value) as(
    
    select 1 ,   'test1'
    union select 2,    'test2'
    union select 2 ,   'test3'
    union select 3 ,   'test4'
    ),cte2 as (
    select *,row_number() over (partition by id order by value desc) as rn 
    from cte
    )
    select *
    from cte2 
    where rn=1


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    • Proposed as answer by Naomi N Friday, March 18, 2016 4:18 PM
    Friday, March 18, 2016 3:50 PM
  • A simple GROUP BY with MAX:

    Select id, Max(Value) as Value

    FROM yourtable

    GROUP by id

    • Proposed as answer by Naomi N Friday, March 18, 2016 4:18 PM
    • Marked as answer by Lio1972 Friday, March 18, 2016 5:31 PM
    Friday, March 18, 2016 3:55 PM