I am really confused about the query that needing to return top N rows having biggest values on particular column.
For example, if the rows N-1, N, N + 1
have same values. Must I return just top N
or top N + 1
rows.
To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement. Here is the alternate query to select first 10 elements.
The MySQL Solution If you're working with MySQL, you can combine MAX() with the GREATEST() function to get the biggest value from two or more fields. Here's the syntax for GREATEST: GREATEST(value1,value2,...) Given two or more arguments, it returns the largest (maximum-valued) argument.
MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With