CODEWITHSUNDEEP
mysqlsqlsorting
Ivan
Ivan

Reputation: 163

MySQL ORDER BY + LIMIT + OFFSET statements: how to OFFSET firts and only then sort with ORDER BY

I have a table which consists of ID, NAME, PRICE and DATE columns.

I'm trying to write a pager-style navigation because there are plenty of entries in the table, so looking at the whole SELECT's output had become uncomfortable.

I've written the following request:

SELECT id
      , name
      , price
      , date 
  FROM mytable 
 ORDER 
    BY name asc 
 LIMIT 30 OFFSET _here's_my_offset_depending_on_the_current_position_

This works fine only as in the example. When I try to sort it, say, by price, it seems that MYSQL first sorts the whole table with ORDER BY and only after it makes an offset.

How do I change this behavior, in other words how do I make an offset and only than sort the resulting rows by whaterver I like?

Upvotes: 1

Views: 2903

Answers (1)

rootatdarkstar
rootatdarkstar

Reputation: 1526

It's can be easy if you use subquery:

SELECT * FROM (
  SELECT id, name, price, date FROM mytable LIMIT 30 OFFSET _offset_
) AS page ORDER BY page.name asc

Upvotes: 2

Related Questions