Returning a range of rows in MySQL relative to either beginning or end of the result set

Question

I've got a query that is returning a set of rows. I want to LIMIT what actually gets returned to some subset of these rows by range, relative either to the beginning or end of the range. I can do this easily if the range is only relative to the beginning. For instance, if I want to return rows 5-7 I can do:

SELECT * FROM <table> WHERE <condition> ORDER BY rowid ASC LIMIT 5,2

The only translation I need to do is from (index0,index1) to offset,length where offset=index0 and length=index1-index0.

But I also am trying to allow the range to be specified relative to the end of the range in a single query, i.e. without running a query first to determine the number of rows and then a second query based on this information. So for example if I specify a row range of (-5,-1) this means that I want the last five rows returned. I cannot pass a negative value to LIMIT.

In reading similar questions, one proposed solution seemed to be to change the ORDER of the query. So I suppose I could do:

SELECT * FROM <table> WHERE <condition> ORDER BY rowid DESC LIMIT 1,5

Now I have two problems. First, the returned set is in the wrong order, I still want it return in ascending order. So now I have to have a subquery to reorder everything:

SELECT * FROM (SELECT * FROM <table> WHERE <condition> ORDER BY rowid DESC LIMIT 1,5) AS x ORDER BY x.rowid ASC;

I'm not sure if there is a better way to do it than that, but there is a second issue: this does not work if the starting and ending part of the range are mixed as to what they are relative to. Suppose I want to return the range (10,-2) which is all rows from the tenth to the next to last. In this case, neither of the above approaches will work.

I also saw where the function mysql_num_rows() was mentioned although it did not give example SQL of how to use it.

SELECT * FROM <table> WHERE <condition> ORDER BY rowid DESC LIMIT 10,mysql_num_rows()-2;

But when I try to run this query, I get this error:

ERROR 1327 (42000): Undeclared variable: mysql_num_rows

Answer 1

What about reversing your approach? Asking for rows in range (10, -2) means "everything except first nine rows and last one".

You can translate it into

select * from yourTable order by rowid asc
minus
select * fom yourTable order by rowid asc limit 9
minus
select * fom yourTable order by rowid desc limit 1

Edit

Since MySQL does not support MINUS, the query above could be rewritten using left join instead

select  t1.*
from    yourTable t1
left join
        (select rowid fom yourTable order by rowid asc limit 9) t2
on      t1.rowid = t2.rowid
left join
        (select rowid fom yourTable order by rowid desc limit 1) t3
on      t1.rowid = t3.id
where   t2.rowid is null and t3.rowid is null
order by t1.rowid asc

Answer 2

This is based on a different answer that was given to this question, which used MINUS. Unfortunately, MySQL does not support this operator. I used NOT IN instead, and furthermore I had to wrap my query inside additional queries to avoid the MySQL issue of lack of support for LIMIT being in an IN sub-query.

So the premise of the solution as provided by the other answer is to treat a positive starting index and a negative ending indexes as a different case from both indicies negative or positive. Then, select everything but exclude the range at the start and finish. The actual code that MySQL likes and which works for the example range of (10,-2) is:

SELECT *
FROM <table>
WHERE <conditions>
AND rowid NOT IN
(
    SELECT * FROM (
        SELECT rowid FROM <table> WHERE <conditions> ORDER BY rowid ASC LIMIT 9
    )
)
AND rowid NOT IN
(
    SELECT * FROM (
        SELECT rowid FROM <table> WHERE <conditions> ORDER BY rowid DESC LIMIT 1
    )
)

Or more generally, for the range (i0,i1) where i0>=0 and i1<0, replace 9 with i0-1 and 1 with -i1-1. Of course, if either of these values are less than one, that portion of the query can be excluded.