How do I write a query that outputs the row number as a column?
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.
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
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()
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.
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
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowId