{"id":1717,"date":"2018-11-10T17:41:05","date_gmt":"2018-11-10T10:41:05","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1717"},"modified":"2024-04-16T10:07:28","modified_gmt":"2024-04-16T03:07:28","slug":"sqlite-nth_value","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-window-functions\/sqlite-nth_value\/","title":{"rendered":"SQLite NTH_VALUE() Function"},"content":{"rendered":"\n

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

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

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

NTH_VALUE(expression, N) \nOVER (\n    PARTITION BY expression1, expression2,...\n    ORDER BY expression1 [ASC | DESC], expression2,..\n    frame_clause\n)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
expression<\/h3>\n\n\n\n
It is a valid expression evaluated against the Nth row in the window frame. The expression must return a single value. A subquery or another window function is not allowed in the expression<\/code>.<\/p>\n\n\n\n
N<\/h3>\n\n\n\n
It specifies the Nth<\/code> row in the window frame specified by the frame_clause<\/code>. N must be a positive integer e.g., 1, 2, and 3.<\/p>\n\n\n\n
Notice that if the Nth<\/code> row does not exist, the NTH_VALUE()<\/code> function will return NULL<\/code>.<\/p>\n\n\n\n
PARTITION BY<\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divided the rows into partitions a to which the NTH_VALUE()<\/code> function applies. The PARTITION BY<\/code> clause is optional. If you omit it, by default, the NTH_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 specifies the order of rows in each partition to which the NTH_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 out the window frame clause tutorial<\/a>.<\/p>\n\n\n\n
The FROM FIRST<\/code> instructs the NTH_VALUE()<\/code> function to start calculation at the first row of the window frame.<\/p>\n\n\n\n
Notice that SQL standard supports both FROM FIRST<\/code> and FROM LAST<\/code> clauses as follows:<\/p>\n\n\n
NTH_VALUE(expression, N) \nFROM {FIRST | LAST}\nOVER (\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
However, SQLite only supports FROM FIRST<\/code> behavior implicitly. You can achieve the effect of the FROM LAST<\/code> by reversing the ORDER BY<\/code> ordering.<\/p>\n\n\n\n
In addition, the SQL standard defines a RESPECT NULLS<\/code> or IGNORE NULLS<\/code> option for the NTH_VALUE()<\/code> function. However, this feature is not implemented in SQLite. The default behavior is always the same as the SQ standard’s default which is RESPECT NULLS<\/code>.<\/p>\n\n\n\n
SQLite NTH_VALUE() function examples<\/h2>\n\n\n\n
See the following tracks<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Using SQLite NTH_VALUE() function over the result set<\/h3>\n\n\n\n
The following statement uses the NTH_VALUE()<\/code> function to return all the tracks and also the second-longest track from the tracks<\/code> table: :<\/p>\n\n\n
SELECT<\/span>\n    Name<\/span>,\n    Milliseconds Length<\/span>,\n    NTH_VALUE<\/span>(name<\/span>,2<\/span>) OVER<\/span> (\n        ORDER<\/span> BY<\/span> Milliseconds DESC<\/span>\n    ) SecondLongestTrack\nFROM<\/span>\n    tracks;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
In this example, the ORDER BY<\/code> clause sorted the result set derived from the FROM<\/code> clause by the track’s lengths in ascending order and the NTH_VALUE()<\/code> function returned the second row from the result set which is the second longest track.<\/p>\n\n\n\n
Using SQLite NTH_VALUE() function over the partition<\/h3>\n\n\n\n
The following example finds the second-longest track in every album:<\/p>\n\n\n
SELECT<\/span>\n    AlbumId,\n    Name<\/span>,\n    Milliseconds Length<\/span>,\n    NTH_VALUE<\/span> ( Name<\/span>,2<\/span> ) OVER<\/span> (\n        PARTITION<\/span> BY<\/span> AlbumId\n        ORDER<\/span> BY<\/span> Milliseconds DESC<\/span>\n        RANGE<\/span> BETWEEN<\/span> \n            UNBOUNDED<\/span> PRECEDING<\/span> AND<\/span> \n            UNBOUNDED<\/span> FOLLOWING<\/span>\n    ) AS<\/span> SecondLongestTrack \nFROM<\/span>\n    tracks;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQLite<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  • The PARTITION BY<\/code> clause divided the tracks by the album id into partitions.<\/li>\n\n\n\n
  • The ORDER BY<\/code> clause sorted the tracks in each album by their lengths in descending order.<\/li>\n\n\n\n
  • The window frame starts at the first row and ends at the last row in each partition.<\/li>\n\n\n\n
  • Then the NTH_VALUE()<\/code> function is applied to each partition separately to get the value from the second row in each partition.<\/li>\n<\/ul>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the NTH_VALUE()<\/code> function to get the value of the Nth row from 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