Menu
I have a query ordered by NAME that return smt like this:
ID NAME
2121927 AAA
2123589 AAB
2121050 AAC
2463926 BBB ---> known ID
2120595 CCC
2122831 DDD
2493055 EEE
2123583 EEF
I need to know the next ID and the prev ID (if exists) of known ID && NAME How is it possible with only 1 query ?
689
You can use UNION to get the previous and next record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.
All replies. select <column list you want> from <your table name> order by ProductName offset 100 rows fetch next 100 rows only; That will skip the first 100 rows (in order by ProductName) and return the next 100 rows.
1) You can use MAX or MIN along with OVER clause and add extra condition to it. The extra condition is "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" which will fetch previous row value. Check this: SELECT *,MIN(JoiningDate) OVER (ORDER BY JoiningDate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS EndDate.
SELECT MAX (column_name) FROM table_name WHERE column_name NOT IN (SELECT Max (column_name) FROM table_name); First we selected the max from that column in the table then we searched for the max value again in that column with excluding the max value which has already been found, so it results in the 2nd maximum value.
SELECT *,
'next'
FROM table
WHERE `name` > 'BBB'
ORDER BY `name`
LIMIT 1
UNION
SELECT *,
'previous'
FROM table
WHERE `name` < 'BBB'
ORDER BY `name` DESC
LIMIT 1
If you don't know particular BBB name field value - you could replace it with subquery like SELECT name FROM table WHERE id = 42, where 42 is the known ID value.
140
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
