sql - Only get the row with the highest value

Question

I have data (Link 1) and I expect the result (Link 2) [only get the row with the highest value of "Revision" Column if "Serial" Column are same] as following:

[Link 1] :

[Link 2] :

Anyone can help me solve this issue ? I appreciate your help.

Thanks.

question from:https://stackoverflow.com/questions/65831256/only-get-the-row-with-the-highest-value

Answer 1

Use not exists as follows:

Select t.*
  From your_table t
 Where not exists
       (Select 1 from your_table tt
         Where tt.serial = t.serial and tt.revision > t.revision)

Or you can use analytical function as follows:

Select * from
(Select t.*,
       Row_number() over (partition by serial order by revision desc) as rn
  From your_table t) t
 Where rn = 1