selecting subsequent records arbitrarily with limit

Question

I want to do a query to retrieve the record immediately after a record for any given record, in a result set ordered by list. I do not understand how to make use of the limit keyword in sql syntax to do this.

I can use WHERE primarykey = number, but how will limiting the result help when I will only have one result?

How would I obtain the next record with an arbitrary primary key number?

I have an arbitrary primary key, and want to select the next one ordered by date.

Answer 1

This will emulate the LEAD() analytic function (i. e. select the next value for each row from the table)

SELECT  mo.id, mo.date,
        mi.id AS next_id, mi.date AS next_date
FROM    (
        SELECT mn.id, mn.date,
               (
               SELECT  id
               FROM    mytable mp
               WHERE   (mp.date, mp.id) > (mn.date, mn.id)
               ORDER BY
                       mp.date, mp.id
               LIMIT 1
               ) AS nid
        FROM   mytable mn
        ORDER BY
               date
        ) mo,
        mytable mi
WHERE mi.id = mo.nid

If you just want to select next row for a given ID, you may use:

SELECT  *
FROM    mytable
WHERE   (date, id) >
        (
        SELECT  date, id
        FROM    mytable
        WHERE   id = @myid
        )
ORDER BY
        date, id
LIMIT 1

This will work most efficiently if you have an index on (date, id)

Answer 2

How about something like this, if you're looking for the one after 34

SELECT * FROM mytable WHERE primaryKey > 34 ORDER BY primaryKey LIMIT 1

Answer 3

Might be as simple as:

select *
from mytable
where datecolumn > (select datecolumn from mytable where id = @id)
order by datecolumn
limit 1

(Edited after comments)