MySql Query, Select greater than

Question

I've got a table, called faq_questions with the following structure:

id int not_null auto_increment,
question varchar(255),
sort_order int

I'm attempting to build a query that given a sort order, selects the row with the next highest sort order.

Example:

id  question                sort_order
1   'This is question 1'    10
2   'This is question 2'    9
3   'This is another'       8
4   'This is another one'   5
5   'This is yet another'   4

Ok, so imagine I pass in 5 for my known sort order (id 4), I need it to return the row with id 3. Since there's no guarantee that sort_order will be contiguous I can't just select known_sort_order + 1.

Thanks!

Answer 1

It seems too simple, but it looks like what you need:

SELECT id,question FROM `questions` 
WHERE `sort_order` > sort_order_variable
ORDER BY sort_order ASC 
LIMIT 1

Answer 2

You can do it with TOP or LIMIT:

SELECT TOP 1 * FROM faq_questions WHERE sort_order > 5 ORDER BY sort_order ASC

but that's not as elegant or portable as

SELECT *
FROM faq_questions AS f1
LEFT JOIN faq_questions AS f2
ON f1.sort_order > f2.sort_order
AND f2.sort_order = 5
LEFT JOIN faq_questions AS f3
ON f3.sort_order BETWEEN f1.sort_order AND f2.sort_order
WHERE f3.id IS NULL

Answer 3

SELECT 
    id, question, sort_order
FROM faq_questions 
WHERE sort_order in 
(SELECT 
        MIN(sort_order) 
    FROM faq_questions 
    WHERE sort_order > ?);

That seems to work

Answer 4

SELECT * FROM table_name WHERE sort_order > 5 ORDER BY sort_order ASC LIMIT 1