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. >> >> >>