Menu
I have a database, and I want to find out the previous and next record ordered by ID, using a single query. I tried to do a union but that does not work. :(
SELECT * FROM table WHERE `id` > 1556 LIMIT 1
UNION
SELECT * FROM table WHERE `id` <1556 ORDER BY `product_id` LIMIT 1
Any ideas? Thanks a lot.
299
We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table. And then we can select the entry which we want to retrieve.
Overview of SQL Server LAG() function In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on. The LAG() function can be very useful for comparing the value of the current row with the value of the previous row.
MySQL LIKE with Percentage % Wildcard: >> SELECT TeachName, subject FROM data. teacher WHERE subject LIKE 'C%'; Use of the percentage sign before the pattern means that the pattern will match the last location of a value.
You need to change up your ORDER BY:
SELECT * FROM table WHERE `id` > 1556 ORDER BY `id` ASC LIMIT 1
UNION
SELECT * FROM table WHERE `id` < 1556 ORDER BY `id` DESC LIMIT 1
This ensures that the id field is in the correct order before taking the top result.
You can also use MIN and MAX:
SELECT
*
FROM
table
WHERE
id = (SELECT MIN(id) FROM table where id > 1556)
OR id = (SELECT MAX(id) FROM table where id < 1556)
It should be noted that SELECT * is not recommended to have in production code, though, so name your columns in your SELECT statement.
190
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
