{"id":1708,"date":"2018-11-10T15:35:04","date_gmt":"2018-11-10T08:35:04","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1708"},"modified":"2024-04-16T10:20:45","modified_gmt":"2024-04-16T03:20:45","slug":"sqlite-last_value","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-last_value\/","title":{"rendered":"SQLite LAST_VALUE() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite LAST_VALUE()<\/code> function to get the value of the last row in a specified window frame.<\/p>\n\n\n\n

Introduction to the LAST_VALUE() function<\/h2>\n\n\n\n

The LAST_VALUE()<\/code> is a window function<\/a> that allows you to obtain the value of the last row in a specified window frame.<\/p>\n\n\n\n

Here is the syntax of the LAST_VALUE()<\/code> function:<\/p>\n\n\n

LAST_VALUE(expression) OVER (\n    PARTITION BY expression1, expression2,...\n    ORDER BY expression1 [ASC | DESC], expression2,..\n    frame_clause\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the syntax in more detail:<\/p>\n\n\n\n
PARTITION BY<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divides the result set into partitions by one or more criteria to which the LAST_VALUE()<\/code> function applies. The PARTITION BY<\/code> clause is optional. If you skip it, the LAST_VALUE()<\/code> function will treat the whole result set as a single partition.<\/p>\n\n\n\n
ORDER BY<\/h3>\n\n\n\n
The ORDER BY<\/code> clause sorts the rows in each partition to which the LAST_VALUE()<\/code> function applies.<\/p>\n\n\n\n
frame_clause<\/h3>\n\n\n\n
The frame_clause<\/code> defines the subset (or the frame) of the current partition. For more detailed information on the frame clause, check it out the window frame<\/a> clause tutorial.<\/p>\n\n\n\n
SQLite LAST_VALUE() function examples<\/h2>\n\n\n\n
We’ll use the following tracks<\/code> table from the sample database<\/a> for the demonstration purpose:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using the LAST_VALUE() function over the result set example<\/h3>\n\n\n\n
The following example uses the LAST_VALUE()<\/code> function to return the track name, the track’s length in minutes, and the longest track of the album id 4:<\/p>\n\n\n
SELECT<\/span>\n  Name<\/span>,\n  printf ('%.f minutes'<\/span>, Milliseconds \/ 1000<\/span> \/ 60<\/span>) AS<\/span> Length<\/span>,\n  LAST_VALUE<\/span> (Name<\/span>) OVER<\/span>                           (\n    ORDER<\/span> BY<\/span>\n      Milliseconds RANGE<\/span> BETWEEN<\/span> UNBOUNDED<\/span> PRECEDING<\/span>\n      AND<\/span> UNBOUNDED<\/span> FOLLOWING<\/span>\n  ) AS<\/span> LongestTrack\nFROM<\/span>\n  tracks\nWHERE<\/span>\n  AlbumId = 4<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
In this example, the ORDER BY<\/code> clause sorted the tracks from the album id 4 by their lengths and the LAST_VALUE()<\/code> function selected the last track from the result set which is the longest track.<\/p>\n\n\n\n
The following frame clause defines the window frame that starts at the first row and ends at the last row of the partition:<\/p>\n\n\n
RANGE BETWEEN UNBOUNDED PRECEDING AND \n            UNBOUNDED FOLLOWING\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that we used the printf()<\/code> function to format the length of the track in minutes.<\/p>\n\n\n\n
2) Using the LAST_VALUE() over partitions example<\/h3>\n\n\n\n
The following example shows all tracks from all albums. In addition, it shows the shortest track for each album:<\/p>\n\n\n
SELECT<\/span>\n  AlbumId,\n  Name<\/span>,\n  printf ('%.f minutes'<\/span>, Milliseconds \/ 1000<\/span> \/ 60<\/span>) AS<\/span> Length<\/span>,\n  LAST_VALUE<\/span> (Name<\/span>) OVER<\/span>                           (\n    PARTITION<\/span> BY<\/span> AlbumId\n    ORDER<\/span> BY<\/span> Milliseconds DESC<\/span> \n    RANGE<\/span> BETWEEN<\/span> UNBOUNDED<\/span> PRECEDING<\/span>\n      AND<\/span> UNBOUNDED<\/span> FOLLOWING<\/span>\n  ) AS<\/span> ShortestTrack\nFROM<\/span>\n  tracks;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture displays the partial output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  • First, the PARTITION BY<\/code> clause divided the tracks by album id.<\/li>\n\n\n\n
  • Then, the ORDER BY<\/code> clause sorted tracks by their lengths from long to short.<\/li>\n\n\n\n
  • Finally, the LAST_VALUE()<\/code> selected the last track in each window frame which is the shortest track in each album.<\/li>\n<\/ul>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the LAST_VALUE()<\/code> function to obtain the value of the last row in a specified window frame.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
expression<\/h3>\n\n\n\n
It is a valid expression evaluated against the last row in the window frame. The expression must return a single result set. It is not allowed to use a subquery<\/a> or another window function<\/a> in the expression<\/code>.<\/p>\n\n\n\n