DEV Community

Dendi Handian
Dendi Handian

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

applying LIMIT and OFFSET:

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

Discussion (2)

Collapse
geraldew profile image
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.

Collapse
dendihandian profile image
Dendi Handian Author • Edited on

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