Additional Column for Incremental Numbering in SQL

The Playground Database

I'm using a database from sqlitetutorial.net and SQLite Browser. From the database, I will only use one table: let's say it's the Albums table.

The Base Query

SELECT
    Title
FROM albums
ORDER BY Title

The above query will display one column named 'Title' of the albums and ordered by the title itself in ascending. The goal here is to add additional column to the left named No. with the incremental numbering.

The ROW_NUMBER() Function

Thanks to geraldew's comment 😁

There is a function to make incremental or consecutive number called ROW_NUMBER(). The detail explanation of the function can be found at https://www.sqltutorial.org/sql-window-functions/sql-row_number/.

But I will make it simple here, We can simple modify the above query by adding this function like this:

SELECT
   ROW_NUMBER() OVER (ORDER BY Title) AS 'No.',
   Title
FROM albums

the result is as we expected, it will numbering to the Titles as ordered in ascending.

From here, we can add the pagination support in SQL like LIMIT and OFFSET:

applying LIMIT:

SELECT
    ROW_NUMBER() OVER (ORDER BY Title) AS 'No.',
    Title
FROM albums
LIMIT 10

applying LIMIT and OFFSET:

SELECT
    ROW_NUMBER() OVER (ORDER BY Title) AS 'No.',
    Title
FROM albums
LIMIT 10 OFFSET 10