SQLite FIRST_VALUE() Function

Summary: in this tutorial, you will learn how to use the SQLite FIRST_VALUE() function to obtain the value of the first row in a specified window frame.

Introduction to the FIRST_VALUE() function

The FIRST_VALUE() is a window function that allows you to obtain the value of the first row in a specified window frame.

The following shows the syntax of the FIRST_VALUE() function:

FIRST_VALUE(expression) OVER (
    PARTITION BY expression1, expression2,...
    ORDER BY expression1 [ASC | DESC], expression2,..
    frame_clause
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

is an expression evaluated against the first row in the window frame. The expression must return a single value. It is not possible to use a subquery or another window function in the expression.

PARTITION BY

The PARTITION BY clause distributes rows into partitions by one or more criteria to which the FIRST_VALUE() function applies. The PARTITION BY clause is optional. If you omit it, the FIRST_VALUE() function will treat the whole result set as a single partition.

ORDER BY

The ORDER BY clause specifies the order of the rows in each partition to which the FIRST_VALUE() function applies.

frame_clause

The frame_clause defines the frame of the current partition. For the detailed information on the frame clause, check it out the window frame clause tutorial.

SQLite FIRST_VALUE() function examples

We will use the tracks table from the sample database for the demonstration:

1) Using SQLite FIRST_VALUE() function with ORDER BY clause example

The following statement uses the FIRST_VALUE() function to return the track name, the size in bytes, and the smallest track of the album id 1:

SELECT
    Name,
    printf('%,d',Bytes) Size,
    FIRST_VALUE(Name) OVER (
        ORDER BY Bytes
    ) AS SmallestTrack
FROM
    tracks 
WHERE
    AlbumId = 1;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output: