SQL Query: Get rows where value of certain column is maximum

Question

Here is example of data I'm dealing with:

Ei (first column) is the primary key.

 E0 S0 DT0 V1
 E1 S1 DT1 V1
 E2 S1 DT2 V1
 E3 S1 DT3 V3

I am trying to filter this to get:

 E0 S0 DT0 V1
 E3 S1 DT3 V3

Basically, get rows such that if they have same Si then choose the row with highest/greatest Vi. Vi is of the form w.xy.z (where w, x, y, z are whole numbers).

I don't know how I can do this. Any pointers?

Answer 1

select ei, si, dti, vi from (
    select ei, si, dti, vi,
           row_number() over(partition by si order by vi desc) rw
    from your_tab
) as t where rw = 1;

You can use analytic functions (ROW_NUMBER in this case).

Analytic functions are similar to aggregate functions as they are applied to a group of rows. But they return one value for each row.

PARTITION BY defines a group, ORDER BY defines the ordering inside the group

ROW_NUMBER assigns a sequential row number for each row in the group according to the ORDER BY (for each distinct value of si the first row number is 1)

If you need to retrieve the result with ties, you can use RANK / DENSE_RANK functions. They assign the same row number to the rows which have an equal "weight".

For example

E0 S0 DT0 V1
E1 S1 DT1 V1
E2 S1 DT2 V1
E3 S1 DT3 V3
E4 S1 DT4 V3

RANK / DENSE_RANK would return

E0 S0 DT0 V1
E3 S1 DT3 V3
E4 S1 DT4 V3

ROW_NUMBER

E0 S0 DT0 V1
E3 S1 DT3 V3 (or E4 S1 DT4 V3)