Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)?

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

2 Answers 2

up vote 6 down vote accepted

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 < 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 > 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 > 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.id, l.time, 
  SUBSTRING_INDEX(GROUP_CONCAT(r.time ORDER BY r.time), ',', 1)
FROM 
  idtimes AS l 
  LEFT JOIN idtimes AS r ON (l.id = r.id)
WHERE 
  l.time < r.time
GROUP BY
  l.id, l.time

Explanation:

  • 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.id, 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

Your Answer

 
discard

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.