Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

Chapter 15. Numbering Rows

PROCEDURAL PROGRAMMERS CANNOT seem to shake the idea of a physical row number being exposed to them. The idea that there is no sequential access or ordering in an RDBMS, so “first,” “next,” and “last” are totally meaningless, is lost on them.

The bad news is that many vendors provide such numberings by exposing their underlying physical storage model. The most common method is to auto-increment a counter as new rows are added to a table. This assumes that the SQL product inserts whole rows in a sequence, just as we added records to the end of a magnetic tape. This is not true for SQL engines with parallelism or that work with columns rather than rows.

If you want an ordering, then you need to have a column that defines ...