MySql select only one row with lowest value in column

Question

what would be the mysql query to select the next value from the column

I have a column named postOrder with values 1 2 3 4 5 6 etc i have tried:

SELECT * FROM table WHERE such=such ORDER BY postOrder ASC LIMIT 1

but sometimes i get the wrong row, like 4 instead of 3 I think i can remove the LIMIT 1 clause, but then I would have to do more work with php.

Any ideas how to make sure I always get the next row without having to get all the rows and then sort them with php? thanks.

Your question is unclear. Show your table structure and sample data for better understanding — B. Desai, Oct 08 '17 at 09:25
I think your question is a duplicate from:https://stackoverflow.com/questions/5191503/how-to-select-the-last-record-of-a-table-in-sql — Samer Abu Gahgah, Oct 08 '17 at 09:35

score 0 · Accepted Answer · answered Oct 08 '17 at 09:26

0

If I understood your question correctly, I think the solution would be to use OFFSET like this:

SELECT * FROM table WHERE such=such ORDER BY postOrder ASC LIMIT 1 OFFSET 2;

With that offset you can get the correct value and do some pagination.

answered Oct 08 '17 at 09:26

StefanR

score 0 · Answer 2 · answered Oct 08 '17 at 09:28

You can use offset to fetch next row(s):

select *
from table
order by postOrder
limit 1, 1;
------^----  Offset
---------^-- rows to fetch

or other syntax:

select *
from table
order by postOrder
limit 1 offset 1;

score 0 · Answer 3 · answered Oct 08 '17 at 09:34

0

you can fetch the last value in the postOrder with a query, then add it to the main query conditions. something like this:

SELECT * FROM table
WHERE such = such 
AND postOrder IN (SELECT MAX(postOrder) FROM table)

answered Oct 08 '17 at 09:34

Samer Abu Gahgah

3 Answers3