Error while numbering returned rows sequentially

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

I want to number the returned rows from a select query sequentially.

SELECT C_ID,S_SEQ,
ROWNUMBER() OVER (ORDER BY S_SEQ DESC) AS NUM
FROM VNT;

I am getting the following error:

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: , FROM INTO

Can any one please correct my query.

Gnanas N · Posted: Thu Feb 28, 2008 6:19 pm

Kiranmayee · New User Joined: 09 Jan 2008 Posts: 25 Location: India

No they are not user defined. I searched in net and got his syntax.

Craq Giegerich · Posted: Thu Feb 28, 2008 6:57 pm

For which vendors version of SQL?

Gnanas N · Posted: Thu Feb 28, 2008 7:05 pm

Is this okay?

stodolas · Posted: Thu Feb 28, 2008 7:31 pm

I think your net search was wrong. That seems more like Oracle syntax to me.

Have you looked in the DB2 SQL Reference Manual? I did a search of it for Row_Number and it returned 0 results for it as a function to number rows. I also did OVER( and got no results.

Gnanas N · Posted: Thu Feb 28, 2008 7:40 pm

I got these info by google-ing through.

ROW_NUMBER() OVER (...) is a OLAP function available with DB2 for UNIX, Windows, and OS/2 as I guess.

And

ROW_NUMBER() OVER tells DB2 how you want the rows numbered. If you are selecting Employee data, telling it to ROW_NUMBER() OVER (ORDER BY SALARY DESC) would have the row with the highest SALARY as #1, the second highest as #2, etc.

Select the exact same data with ROW_NUMBER() OVER (ORDER BY LASTNAME) would have a person with a LASTNAME starting with 'A' as #1 and someone with 'Z' as the very last.

Please try these links.

www.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html

www.ibm.com/developerworks/db2/library/techarticle/dm-0401kuznetsov/index.html