How do I get the current and next greater value in one select?
Doing a JOIN is one thing you might need.

SELECT l.id, l.time, r.time FROM 
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id


I suppose the outer join is deliberate, and you want to be getting nulls.  More on that later.

WHERE l.time &lt; r.time ORDER BY l.id ASC, l.time ASC;


You only want the r. row that has the lowest (MIN) time that is higher than the l.time.  That is the place where you need subquerying.

WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time &gt; l.time)


Now to the nulls.  If "there is no next-higher time", then the SELECT MIN() will evaluate to null (or worse), and that itself never compares equal to anything, so your WHERE clause will never be satisfied, and the "highest time" for each ID, could never appear in the result set.

You solve it by eliminating your JOIN, and moving the scalar subquery into the SELECT list :

SELECT id, time, 
    (SELECT MIN(time) FROM idtimes sub 
        WHERE sub.id = main.id AND sub.time &gt; main.time) as nxttime
  FROM idtimes AS main