sqlsql-servermaxgreatest-n-per-group

Simple Query to Grab Max Value for each ID


OK I have a table like this:

ID     Signal    Station    OwnerID
111     -120      Home       1
111     -130      Car        1
111     -135      Work       2
222     -98       Home       2
222     -95       Work       1
222     -103      Work       2

This is all for the same day. I just need the Query to return the max signal for each ID:

ID    Signal    Station    OwnerID
111   -120      Home        1
222   -95       Work        1

I tried using MAX() and the aggregation messes up with the Station and OwnerID being different for each record. Do I need to do a JOIN?


Solution

  • Something like this? Join your table with itself, and exclude the rows for which a higher signal was found.

    select cur.id, cur.signal, cur.station, cur.ownerid
    from yourtable cur
    where not exists (
        select * 
        from yourtable high 
        where high.id = cur.id 
        and high.signal > cur.signal
    )
    

    This would list one row for each highest signal, so there might be multiple rows per id.