How to Get the First Row per Group

The function row_number() returns the position of a record in an ordered group. To obtain the first record in a group of records, you can use the with clause to create common table expression including row_number() to obtain the position of each row in the group. Later with an outer query you can filter rows by position.

WITH 
employee AS (
   SELECT 1 as emp_id, 'Smith' as last_name, 80000.00 as salary, 101 as dep_id
   UNION ALL
   SELECT 2, 'Sexton',  70000.00 , 101 UNION ALL
   SELECT 3, 'Maly',  90000.00 , 102 UNION ALL
   SELECT 4, 'Verstap', 125000.00 , 102
),
rows_and_position AS ( 
   SELECT emp_id, last_name, salary, dep_id,
          row_number() over (partition by dep_id order by salary desc) as position
   FROM   employee
       )
select dep_id, last_name, salary
from  rows_and_position
where position = 1

The approach presented here can be used to obtain records in any position in a group.

IN THIS PAGE