mysql select from n last rows

by

Starting from the answer given by @chaos, but with a few modifications:

  • You should always use ORDER BY if you use LIMIT. There is no implicit order guaranteed for an RDBMS table. You may usually get rows in the order of the primary key, but you can’t rely on this, nor is it portable.

  • If you order by in the descending order, you don’t need to know the number of rows in the table beforehand.

  • You must give a correlation name (aka table alias) to a derived table.

Here’s my version of the query:

SELECT `id`
FROM (
    SELECT `id`, `val`
    FROM `big_table`
    ORDER BY `id` DESC
    LIMIT $n
) AS t
WHERE t.`val` = $certain_number;

More Related Contents:

  1. Checking multiple columns for one value
  2. What is better subquery vs literal on IN clause in mysql
  3. quick selection of a random row from a large table in mysql
  4. MySQL: is a SELECT statement case sensitive?
  5. JOIN queries vs multiple queries
  6. MySQL indexes – what are the best practices?
  7. Combine Multiple child rows into one row MYSQL
  8. Subqueries with EXISTS vs IN – MySQL
  9. MySQL select statement with CASE or IF ELSEIF? Not sure how to get the result
  10. How to get an age from a D.O.B field in MySQL?
  11. MySQL select with CONCAT condition
  12. Difference in MySQL JOIN vs LEFT JOIN
  13. 60 million entries, select entries from a certain month. How to optimize database?
  14. How does MySQL’s ORDER BY RAND() work?
  15. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  16. MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?
  17. MySQL INSERT INTO … VALUES and SELECT
  18. MySQL error 1241: Operand should contain 1 column(s)
  19. How to select one row randomly taking into account a weight?
  20. Compare only day and month with date field in mysql
  21. mysql, ifnull vs coalesce, which is faster?
  22. mysql select sum group by date
  23. MySQL – SQL_BIG_SELECTS
  24. MySQL INNER JOIN select only one row from second table
  25. MySQL Select Query – Get only first 10 characters of a value
  26. What is the default MySQL JOIN behaviour, INNER or OUTER?
  27. Insert data into table with result from another select query
  28. How to use Table output from stored MYSQL Procedure
  29. MySQL cartesian product between two SELECT statements
  30. MySQL Select Multiple VALUES

Leave a Comment