How do I write a query that outputs the row number as a column?

Question

How do I write a query that outputs the row number as a column? This is DB2 SQL on an iSeries.

eg if I have

table Beatles:

John
Paul
George
Ringo

and I want to write a statement, without writing a procedure or view if possible, that gives me

1 John
2 Paul
3 George
4 Ringo

Answer 1

Check out the row_number() function; you should be able to do this in DB2 via:

SELECT row_number(), first_name FROM beatles

I'm almost certain this is not part of the SQL standard though, so it is not likely to be portable should that ever be an issue.

Answer 2

SELECT ROW_NUMBER() OVER (ORDER BY beatle_name ASC) AS ROWID, * FROM beatles

Answer 3

SELECT ROW_NUMBER() OVER(ORDER BY BEATLE_NAME) ROWNUM,BEATLE_NAME FROM BEATLES;