Menu
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
718
To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row# . You must move the ORDER BY clause up to the OVER clause. SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.
SET @sql = CONCAT('SELECT Meeting_id, ', @sql, ' FROM Meeting WHERE <condition> GROUP BY Meeting_id'); Similarly, you can also apply JOINS in your SQL query while you display row values as columns in MySQL. After you convert row to column in MySQL, you can use a charting tool to plot the result in a table.
ROW_NUMBER function is a SQL ranking function that assigns a sequential rank number to each new record in a partition. When the SQL Server ROW NUMBER function detects two identical values in the same partition, it assigns different rank numbers to both.
SELECT ROW_NUMBER() OVER (ORDER BY beatle_name ASC) AS ROWID, * FROM beatles
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.
28
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
