Menu
table {
id: long
name: string
}
1235 Fred
1902 Trever
5123 George
6467 Derek
7868 Joe
8972 Bob
9272 Alf
9842 Hank
I want to return 2 records prior to that of Joes, in ascending order.
i.e The correct values should be:
5123 George
6467 Derek
Any thoughts? FYI:
Returns incorrect rows:
select * from table with id<7868 order by id asc limit 2
Returns incorrect sort order:
select * from table with id<7868 order by id desc limit 2
247
No, you can't LIMIT subqueries arbitrarily (you can do it to a limited extent in newer MySQLs, but not for 5 results per group). This is a groupwise-maximum type query, which is not trivial to do in SQL.
The OFFSET FETCH clause allows you to skip N first rows in a result set before starting to return any rows. In this syntax: The ROW and ROWS , FIRST and NEXT are the synonyms. Therefore, you can use them interchangeably.
Using SKIP and ORDER BYWhen you use the SKIP option in a query with an ORDER BY clause, you can exclude the first offset rows that have the lowest values according to the ORDER BY criteria. You can also use SKIP to exclude rows with the highest values, if the ORDER BY clause includes the DESC keyword.
SELECT * FROM
(select * from table where id<7868 order by id desc limit 2) AS foo
ORDER BY ID ASC
Try:
Select * from (
select * from table with id<7868
order by id desc limit 2
) as t order by id asc
Doing the subquery lets you first get the correct rows, then you can re-order them afterwards
45
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
