CODEWITHSUNDEEP
phpmysql
Ivan Seidel
Ivan Seidel

Reputation: 2362

Select on Mysql inverse order

i have a MySql table that consists of 2 basic things: The id and a value. To show that on my page, i need to select, for example, the last 100 rows on reversed order. So imagine that someone is putting data on it:

Id, value
1, 10
2, 9
3, 21
4, 15

i need, to select the last "3" rows (LIMIT + ORDER Clause), but not like this: 4,3,2 but like this: 2,3,4.

I know how to do that on code, but maybe there is a simple solution for that on Mysql and i don`t know. Thanks

My SQL Query is like this right now:

SELECT `Data`.`id`, `Data`.`log_id`, `Data`.`value`, `Data`.`created` FROM `control_panel`.`datas` AS `Data` WHERE `Data`.`id` > 1000 AND `Data`.`log_id` = (2) ORDER BY `Data`.`id` DESC LIMIT 100

Upvotes: 1

Views: 4751

Answers (3)

Nikson Kanti Paul
Nikson Kanti Paul

Reputation: 3440

simply 

 SELECT t.*
 (SELECT * FROM table_name 
      ORDER BY column_name DESC 
      LIMIT 0,3) t
 ORDER BY t.column_name ASC

use DESC to descending order, ASC to increasing order

Upvotes: 0

MDrollette
MDrollette

Reputation: 6927

One way to do this would be with a sub-select.

SELECT *
FROM (SELECT * FROM table_name ORDER BY id DESC LIMIT 3) tmp
ORDER BY id ASC

Upvotes: 4

Zane Bien
Zane Bien

Reputation: 23125

You need to wrap the first ORDER BY in a subselect which will return a limited selection ordered in descending order, then you can order that result in the outer query in ascending order: 

SELECT
    a.*
FROM
    (
        SELECT id, value
        FROM tbl
        ORDER BY id DESC
        LIMIT 3
    ) a
ORDER BY
   a.id

Upvotes: 5

Related Questions