Nick Sinas Nick Sinas - 1 year ago 77
SQL Question

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?

Answer Source

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

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

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download