increment field...

[Brian Percival]

well, this is strictly not JDBC but a sql question, I have mssql 2005, is there any way in my SELECT query to get the row numbers also? meaning, in addition to the fields theat I SELECT, I want an extra field which just has 1,2,3,...rowcount.

Is there any function?

Brian

[Chad Clites]

Good question. I don't know of any function that will do that, so I am sort of curious also. I can't find anything in any of my reference materials. Is there some constraint that prevents you from just adding the numbering as you iterate through the resultSet? Or is there an option to create your own function?

[Brian Percival]

well, I wish to accomplish this in a single sql select, because this is going to be a part of a bigger complex sql statement, and I want to keep it that way instead of breaking it.

I would think sql should have some sort of a utility function like this.

[Chad Clites]

And indeed it appears that there is:row_number()
http://www.databasejournal.com/features/mssql/article.php/3572301

Apparently support differs from database to database, but now that you know what you are looking for, you should be able to find it.

[Brian Percival]

Chad, that's what I wanted. thanks alot..

[krishnamoorthy kitcha]

Hi Brian Percival

You were asked the rownumber in the selected query ,

Yes It is possible , check this one

but one restriction is there

you can't use like this select * from tablename

but you can use like this select col1,col2,...coln from tablename

Check this one




select col1,col2...coln,row_number() over(order by col1 desc) as row from table


Check and tell me your comments

Rgks
k.krishnamoorthy