CODEWITHSUNDEEP
sqldb2
nearly_lunchtime
nearly_lunchtime

Reputation: 12933

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

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

Upvotes: 14

Views: 66413

Answers (3)

RAJNISH KUMAR
RAJNISH KUMAR

Reputation: 11

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

Upvotes: 1

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 31

Andrzej Doyle
Andrzej Doyle

Reputation: 103797

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.

Upvotes: 3

Related Questions