MySQL: Select top n max values?

by

If you do:

select *
from t
order by value desc
limit N

You will get the top N rows.

If you do:

select *
from t join
     (select min(value) as cutoff
      from (select value
            from t
            order by value
            limit N
           ) tlim
    ) tlim
    on t.value >= tlim;

Or you could phrase this a bit more simply as:

select *
from t join
     (select value
      from t
      order by value
      limit N
    ) tlim
    on t.value = tlim.value;

The following is conceptually what you want to do, but it might not work in MySQL:

select *
from t
where t.value >= ANY (select value from t order by value limit N)

More Related Contents:

  1. Calculate Average of the largest values in multiple columns
  2. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  3. SQL Query to get column values that correspond with MAX value of another column?
  4. How to get the max of two values in MySQL?
  5. How to SELECT by MAX(date)?
  6. Deleting a row based on the max value
  7. how to delete data from table A besides the customer service and director data in table B?
  8. Get records with max value for each group of grouped SQL results
  9. How to store multiple options in a single table?
  10. How to populate a table with a range of dates?
  11. Get the new record primary key ID from MySQL insert query?
  12. brew install mysql on macOS
  13. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  14. MySQL: multiple tables or one table with many columns? [closed]
  15. How do I check to see if a value is an integer in MySQL?
  16. Add 2 hours to current time in MySQL?
  17. Store image to database blob; retrieve from db into Picturebox
  18. MySQL trigger On Insert/Update events
  19. My MySQL trigger doesn’t work, simple syntax, not complicated
  20. How can VBA connect to MySQL database in Excel?
  21. Select a column with a keyword name
  22. Sum results of a few queries and then find top 5 in SQL
  23. ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  24. Use Composite Primary Key as Foreign Key
  25. WooCommerce: Finding the products in database
  26. How to auto-load MySQL on startup on OS X Yosemite / El Capitan
  27. Mysql performance on 6 million row table
  28. MySQL Select Multiple VALUES
  29. Convert SQL Server query to MySQL [duplicate]
  30. Dump only the data with mysqldump without any table information?

Leave a Comment