Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

FIRST_VALUE, LAST_VALUE ​

Return the first or last value associated with a window frame, given an ordered set of rows.

FIRST_VALUE | LAST_VALUE ( expression [ RESPECT NULLS | IGNORE NULLS ] )
OVER { window_name | ( [ window_definition ] ) }
RESPECT NULLS | IGNORE NULLS

When NULL values appear in expression, respect or ignore them as potential first and last values. RESPECT NULLS is the default.

If IGNORE NULLS is used, return the first value or last value in the window frame that is not NULL. If RESPECT NULLS is used, return the first value or last value whether it is NULL or not. If all values in the frame are NULL, NULL always qualifies as the first or last value.

window_definition

See Syntax for Window Functions. The ORDER BY clause is not required but is recommended for determining "first" and "last" values based on an ordered set of rows.

Examples ​

In this example, the "first value" in the frame is 1 for London, 2 for Birmingham, and so on (the teamid for an ordered list of cities).

premdb=# select teamid, city, 
first_value(teamid) over(partition by city order by city) 
from team order by 3;
 teamid |     city      | first_value 
--------+---------------+-------------
     1 | London        |           1
    12 | London        |           1
    13 | London        |           1
    15 | London        |           1
    18 | London        |           1
    32 | London        |           1
    41 | London        |           1
    44 | London        |           1
    46 | London        |           1
     2 | Birmingham    |           2
     4 | Birmingham    |           2
    43 | Birmingham    |           2
     3 | Barnsley      |           3
     5 | Blackburn     |           5
     6 | Blackpool     |           6
...

The following two queries demonstrate the use of the IGNORE NULLS option. The FIRST_VALUE function operates on the capacity column, which contains some NULL values. The first query ignores NULL values in the capacity column when they are the first value in the window frame (defined as 1 preceding and 1 following). The only time NULL is returned for the FIRST_VALUE function in this case is when the frame consists of three consecutive rows with NULL capacity values.

premdb=# select teamid, name, city, stadium, capacity, 
first_value(capacity ignore nulls) over(partition by city order by teamid rows between 1 preceding and 1 following) 
from team where city in ('London','Birmingham','Manchester');
 teamid |         name         |    city    |     stadium      | capacity | first_value 
--------+----------------------+------------+------------------+----------+-------------
     2 | Aston Villa          | Birmingham | Villa Park       |    42785 |       42785
     4 | Birmingham City      | Birmingham | St. Andrew's     |    30016 |       42785
    43 | West Bromwich Albion | Birmingham | The Hawthorns    |    27000 |       30016
     1 | Arsenal              | London     | Emirates Stadium |    60260 |       60260
    12 | Charlton Athletic    | London     | The Valley       |    27111 |       60260
    13 | Chelsea              | London     | Stamford Bridge  |    41663 |       27111
    15 | Crystal Palace       | London     | Selhurst Park    |   [NULL] |       41663
    18 | Fulham               | London     | Craven Cottage   |   [NULL] |      [NULL]
    32 | Queens Park Rangers  | London     | Loftus Road      |   [NULL] |       36284
    41 | Tottenham Hotspur    | London     | White Hart Lane  |    36284 |       36284
    44 | West Ham United      | London     | Upton Park       |    35016 |       36284
    46 | Wimbledon            | London     | Selhurst Park    |   [NULL] |       35016
    24 | Manchester City      | Manchester | Etihad Stadium   |    55097 |       55097
    25 | Manchester United    | Manchester | Old Trafford     |    75635 |       55097
(14 rows)

The second query uses RESPECT NULLS (which is equivalent to the default behavior and could be omitted from the syntax). Everything else in the query is the same as the first query. Note that the FIRST_VALUE results are different. Because NULL values are respected, three rows return NULL for the FIRST_VALUE function.

premdb=# select teamid, name, city, stadium, capacity, 
first_value(capacity respect nulls) over(partition by city order by teamid rows between 1 preceding and 1 following) 
from team where city in ('London','Birmingham','Manchester');
 teamid |         name         |    city    |     stadium      | capacity | first_value 
--------+----------------------+------------+------------------+----------+-------------
     2 | Aston Villa          | Birmingham | Villa Park       |    42785 |       42785
     4 | Birmingham City      | Birmingham | St. Andrew's     |    30016 |       42785
    43 | West Bromwich Albion | Birmingham | The Hawthorns    |    27000 |       30016
     1 | Arsenal              | London     | Emirates Stadium |    60260 |       60260
    12 | Charlton Athletic    | London     | The Valley       |    27111 |       60260
    13 | Chelsea              | London     | Stamford Bridge  |    41663 |       27111
    15 | Crystal Palace       | London     | Selhurst Park    |   [NULL] |       41663
    18 | Fulham               | London     | Craven Cottage   |   [NULL] |      [NULL]
    32 | Queens Park Rangers  | London     | Loftus Road      |   [NULL] |      [NULL]
    41 | Tottenham Hotspur    | London     | White Hart Lane  |    36284 |      [NULL]
    44 | West Ham United      | London     | Upton Park       |    35016 |       36284
    46 | Wimbledon            | London     | Selhurst Park    |   [NULL] |       35016
    24 | Manchester City      | Manchester | Etihad Stadium   |    55097 |       55097
    25 | Manchester United    | Manchester | Old Trafford     |    75635 |       55097
(14 rows)

Parent topic:Window Functions