This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes Generalising Rownum for all DataBases Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Generalising Rownum for all DataBases" Watch "Generalising Rownum for all DataBases" New topic

Generalising Rownum for all DataBases

sreenath reddy
Ranch Hand

Joined: Sep 21, 2003
Posts: 415

I have as issue with this rownum.This is supported in different forms in different databases like top in mssql ,limit in mysql and all..i want to genaralise this independent of the type of database because i want to run the query as i wont be knowing the type of the DB used by my user.

can anyone suggest a solution for this ? is there anyway of generating a series of numbers with the query by calling the function in that query repetitively ???

Tom Blough
Ranch Hand

Joined: Jul 31, 2003
Posts: 263
The only way I can think of to be independent of specific implementations of rownum, it to implement your own ranking as a field in the data:

SELECT a.vendor_no,, count(*) AS rank
FROM qryTopSuppliers AS a, qryTopSuppliers AS b
GROUP BY a.vendor_no,

rank will now contain a integer value you can use for your own purposes.

Tom Blough<br /> <blockquote><font size="1" face="Verdana, Arial">quote:</font><hr>Cum catapultae proscriptae erunt tum soli proscripti catapultas habebunt.<hr></blockquote>
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
subject: Generalising Rownum for all DataBases
It's not a secret anymore!