Add Sequential Number Column in SQL Query Result

To add a column with sequential number in SQL query can be done through add a sequence numbered table contains a link key or directly compose in query. The first method sounds unwise, but if the query itself is too complicated, it might not be a bad idea.

The second method is:

SELECT
(
SELECT COUNT(au_id)
FROM Mytable1 AS x
WHERE x.au_id <= y.au_id
) AS Sequence
, au_id
, something_else
FROM Mytable1 AS y
ORDER BY au_id

This constrains here are:
1. Field au_id must be number.
2. The query itself can’t be too complicated.
3. If Mytable is from another query, if won’t work properly in some systems, such as SQL Server.


http://www.databasejournal.com/features/mssql/article.php/3373861/Auto-Number-and-Cumulative-sum-in-SQL-Server-Query-results.htm