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.id, l.time, r.time FROM
idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id
WHERE l.time < r.time ORDER BY l.id 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.id, outer.time,
(SELECT time FROM idtimes WHERE id = outer.id AND time > outer.time
ORDER BY time ASC LIMIT 1)
FROM idtimes AS outer ORDER BY outer.id 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)?
Best Answer
Doing a JOIN is one thing you might need.
I suppose the outer join is deliberate, and you want to be getting nulls. More on that later.
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.
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 :