CODEWITHSUNDEEP
mysqlsqlselectsql-order-by
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

Displaying last x rows of query with limit

I am currently trying to query all values in a table that have a game_id less than 1000058 with a limit defined. For example, If I set limit 3, I would like to get the last 3 rows in ascending order. I am aware that it can be done by setting an offset like limit 2, 3 but I would need to know beforehand the row number. Let say I don't know the row numbers, How can display the last 3 rows of the query in ascending order? SQLFIDDLE

Displays the right values but the order is DESC instead of ASC

SELECT * FROM games WHERE mature = true AND category_id = 1004 AND game_id < 1000058 ORDER BY game_id DESC LIMIT 3;

Displays the right values but I need to know the row number for the offset

SELECT * FROM games WHERE mature = true AND category_id = 1004 AND game_id < 1000058 ORDER BY game_id LIMIT 2,3;

Displays only the first three rows in ascending order

SELECT * FROM games WHERE mature = true AND category_id = 1004 AND game_id < 1000058 ORDER BY game_id LIMIT 3;

Desired result

game_id    game_name     cat_id  mature     description                         published_date
-------    ------------  ------  ------     ---------------------------------   --------------
1000055    Battlefield4  1004    1          Published by EA Digital Illusions   2013-01-01  
1000056    Rainbow6      1004    1          Published by EUbisoft               2015-01-01
1000057    Destiny       1004    1          Published by Bungie                 2014-01-01

Upvotes: 1

Views: 50

Answers (2)

Alaa M. Jaddou
Alaa M. Jaddou

Reputation: 1189

i think this is what you looking for my friend:

SELECT * FROM (
    SELECT * FROM games 
    where game_id < 1000058 
      and mature = true 
      and category_id = 1004 
   ORDER BY game_id DESC
   LIMIT 3
) sub

ORDER BY game_id ASC

Upvotes: 3

Mureinik
Mureinik

Reputation: 311053

You could used a nested query - the inner query is ordered in descending order so you can limit the top three games, and then the outer query sorts the results in ascending order:

SELECT   *
FROM     (SELECT   * 
          FROM     games 
          WHERE    mature = true AND 
                   category_id = 1004 AND 
                   game_id < 1000058 
          ORDER BY game_id DESC
          LIMIT    3) t
ORDER BY game_id ASC

Upvotes: 4

Related Questions