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.
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
SELECT ROW_NUMBER() OVER (ORDER BY Title) AS 'No.', Title FROM albums LIMIT 10
SELECT ROW_NUMBER() OVER (ORDER BY Title) AS 'No.', Title FROM albums LIMIT 10 OFFSET 10