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



1 Answers

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.

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



How to get an incremental “RowId” column in SELECT using ROW_NUMBER()

Removing the PARTITION BY is exactly what is needed. The reason that your numbers look random is that the ORDER BY of the outer query is different from the ORDER BY of your ROW_NUMBER(). All you have to do is make those the same, and the output of the sequence project will have the monotonically increasing value you expect.

Specifically:

SELECT ROW_NUMBER() OVER (ORDER BY TagInfo.[Count] DESC, TagInfo.TagName) AS RowId, * 
FROM
(
   ...
) AS TagInfo
ORDER BY TagInfo.[Count] DESC, TagInfo.TagName

Now you aren't partitioning, and the two ORDER BY clauses match, so you'll get your expected output.

For what it's worth, you technically don't really even care about having an ORDER BY in the ROW_NUMBER(), you just want the same order as the final result set. In that case, you can trick the query engine like so by providing a meaningless ORDER BY clause in the ROW_NUMBER():

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowId

Boom, done!




Related



Tags