• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Ron McLeod
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

increment field...

 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Chad, that's what I wanted. thanks alot..
 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic