MySQL Forums
Forum List  »  Optimizer & Parser

Help with ORDER BY with LIMIT
Posted by: Lukas Pilny
Date: May 09, 2010 02:24PM

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,

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

Options: ReplyQuote

Written By
Help with ORDER BY with LIMIT
May 09, 2010 02:24PM
May 10, 2010 10:59PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.