hi Zoran, the syntax i sent was oracle specific. i for sure know sql server and db2 has equivalents. for mysql i am not sure.
ashok On 7/31/07, Zoran Avtarovski <[EMAIL PROTECTED]> wrote: > > Thanks Ashok, > > Unfortunately MySQL doesn't support the rownum pseudo column, thanks to > your > pointer I was able to find a work around. > > If anybody is interested it involves using variables as such: > > SELECT @rownum:[EMAIL PROTECTED] as rownum, P.* > > FROM (SELECT @rownum:=0) as R, primary_table as P > > It's a simple step to go from there to your solution. > > Thanks again, you a godsend. > > Z. > > > > you can do a nested select ( select within a select within a select ) > and > > get the 'n' number of rows. > > > > something like this : > > select FIELDNAME, USERID from > > ( select FIELDNAME, USERID, rownum num > > from ( select A.FIELDNAME, B.USERID > > from tblA A, tblB B > > where A.configUnitID = ? > > and A.USERUID = B.userUID > > order by A.FIELDNAME ) a > > ) b where num between 50 and 60 > > > > regards > > ashok > > > > On 7/30/07, Zoran Avtarovski <[EMAIL PROTECTED]> wrote: > >> > >> I've come across an issue using iBatis' n+1 implementation in > combination > >> external paging with DisplayTag. The issue lies with with the SQL LIMIT > >> and > >> OFFSET commands on complex queries. > >> > >> My problem is that when I execute a count statement: > >> > >> SELECT COUNT(*) from primary_table > >> > >> I get a resulting integer > >> > >> But when I do the actual query: > >> SELECT * from primary_table > >> LEFT OUTER JOIN secondary_table > >> LEFT OUTER JOIN tertiary_table > >> LIMIT #limit# OFFSET #offset# > >> > >> The limit and ofsset apply to the raw resultset, the size of which is > >> primary size + secondary size + tertiary size. Meaning that The > DisplayTag > >> navigation header never lets the user get the results beyond primary > size > >> on > >> the raw query. > >> > >> The work around I'm using is to execute the count on the complex query, > >> but > >> the navigation info is misleading and I'd rather get it right. > >> > >> I was hoping that somebody had come across this and had a possible > >> solution. > >> > >> The reason we've moved away from DisplayTag's built in paging is that > the > >> result set is at 100K and growing which was giving us a performance > hit. > >> > >> Z. > >> > >> > >> > > >
