How to Find the Maximum Signal Value for Each ID in a SQL Table?

Max Signal Query for Each ID

Question:

Given a table with multiple signal values associated with each ID, how can a query be written to retrieve the maximum signal value for each unique ID? Consider the following example table:

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

Answer:

To retrieve the maximum signal value for each ID without affecting other columns like Station and OwnerID, a self-join can be employed:

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 query joins the table with itself to create a Cartesian product. For each pair of rows, it checks if the signal value of the second row (high) is greater than that of the first row (cur). If a higher signal value is found for the same ID, the current row is excluded.

The result is a table containing only the rows with the maximum signal value for each ID. For the example table, the output would be:

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

The above is the detailed content of How to Find the Maximum Signal Value for Each ID in a SQL Table?. For more information, please follow other related articles on the PHP Chinese website!