Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns

`id` int(11) NOT NULL,
`time` int(20) NOT NULL, [...]

with a compound unique key

UNIQUE KEY `id_time` (`id`,`time`)

so there can be multiple timestamps per id and multiple ids per timestamp.

I'm trying to set up a query where I get all entries plus the next greater time for each entry, if it exists, so it should return e.g.:

| id  | time       | nexttime   |
| 155 | 1300000000 | 1311111111 |
| 155 | 1311111111 | 1322222222 |
| 155 | 1322222222 |       NULL |
| 156 | 1312345678 | 1318765432 |
| 156 | 1318765432 |       NULL |

Right now I am so far:

SELECT, l.time, r.time FROM 
    idtimes AS l LEFT JOIN idtimes AS r ON =
    WHERE l.time < r.time ORDER BY ASC, l.time ASC;

but of course this returns all rows with r.time > l.time and not only the first one...

I guess I'll need a subselect like

SELECT, outer.time, 
    (SELECT time FROM idtimes WHERE id = AND time > outer.time 
        ORDER BY time ASC LIMIT 1)
    FROM idtimes AS outer ORDER BY ASC, outer.time ASC;

but I don't know how to refer to the current time (I know the above is not valid SQL).

How do I do this with a single query (and I'd prefer not to use @variables that depend on stepping though the table one row at a time and remembering the last value)?

share|improve this question
If this question belongs on another site (e.g. stackoverflow), please feel free to move it. – Martin Sep 10 '12 at 9:45
up vote 9 down vote accepted

Doing a JOIN is one thing you might need.

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

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

WHERE l.time < r.time ORDER BY 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 = AND r2.time > 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 = AND sub.time > main.time) as nxttime
  FROM idtimes AS main 
share|improve this answer
This is beautiful, and it was so close to what I tried... Shame on me. Thanks to you. – Martin Sep 10 '12 at 15:22

Before presenting the solution, I should note it is not pretty. It would be much easier if you had some AUTO_INCREMENT column on your table (do you?)

SELECT, l.time, 
  idtimes AS l 
  LEFT JOIN idtimes AS r ON ( =
  l.time < r.time
GROUP BY, l.time


  • Same join as yours: join two tables, the right one only gets the higher times
  • GROUP BY both columns from left table: this ensures we get all (id, time) combinations (which are also known to be unique).
  • For each (, l.time), get the first r.time which is greater than l.time. This happens with first ordering the r.times via GROUP_CONCAT(r.time ORDER BY r.time), the by slicing first token via SUBSTRING_INDEX.

Good luck, and, don't expect good performance if this table is large.

share|improve this answer
Solutions like this are never pretty, but if that's what the client wants.... +1 !!! – RolandoMySQLDBA Sep 10 '12 at 14:52

You can also get what you want from a min() and GROUP BY with no inner select:

SELECT, l.time, min(r.time) 
FROM idtimes l 
LEFT JOIN idtimes r on ( = and r.time > l.time)
GROUP BY, l.time;

I would almost bet a large sum of money that the optimiser turns this into the same thing as Erwin Smout's answer anyway, and it's debatable whether it's any clearer, but there it is for completeness...

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.