Help with ORDER BY with LIMIT

[Lukas Pilny]

Hi all,

I thought this problem was solved thousand times, however I couldn't find anything...

To describe my problem, I would like to sort correctly all rows before LIMIT is applied. As I found in manual, MySQL ends the sorting as soon as it finds the number of rows in the sorted result.
Unfortunately, that makes problem with pagination, as e.g. last surname on page 1 starts "AN...", but first surname on the page 2 starts "AB....", using ORDER BY lastname.

Is there any SQL possibility to get the correct order of all records even if I need to use LIMIT?
I tried things like:
SELECT * FROM ( SELECT * FROM t ORDER BY lastname) AS t1 ORDER BY lastname LIMIT 0, 300
but the result is same as:
SELECT * FROM t ORDER BY lastname LIMIT 0, 300


I cannot use VIEW, because of subquery in the FROM clause (in real query), and that's why I am looking for some hidden trick or advice from you who know... Any ideas?

Thanks a lot in advance,
Lukas



Edited 1 time(s). Last edit at 05/10/2010 01:34AM by Lukas Pilny.