Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

increment field...

 
Brian Percival
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Chad, that's what I wanted. thanks alot..
 
krishnamoorthy kitcha
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic