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

Comments

[geraldew]

I suggest you read the very tutorial page that you gave a link to - as it clearly explains that the Rank function does not guarantee consecutive numbers.

For that you're better off using the Row_Number function. See SQL Window Functions - SQL ROW_NUMBER

p.s. yes there's also the DENSE_RANK() function, but it's not as reliably present across dialects.

[Dendi Handian]

Thanks, I've update the post. Why did I find RANK() first when this ROW_NUMBER() is exists 🤦‍♂️