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 < 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
I always avoid to use subqueries either in SELECT
block or in the FROM
block, because it makes the code "dirtier" and sometimes less efficient.
I think a more elegant way to do it is to:
1. Find the times greater than the time of the row
You can do this with a JOIN
between idtimes table with itself, constraining the join to the same id and to times greater than the time of current row.
You should use LEFT JOIN
to avoid excluding rows where there are no times greater than the one of the current row.
SELECT
i1.id,
i1.time AS time,
i2.time AS greater_time
FROM
idtimes AS i1
LEFT JOIN idtimes AS i2 ON i1.id = i2.id AND i2.time > i1.time
The problem, as you mentioned, is that you have multiple rows where next_time is greater than time.
+-----+------------+--------------+
| id | time | greater_time |
+-----+------------+--------------+
| 155 | 1300000000 | 1311111111 |
| 155 | 1300000000 | 1322222222 |
| 155 | 1311111111 | 1322222222 |
| 155 | 1322222222 | NULL |
| 156 | 1312345678 | 1318765432 |
| 156 | 1318765432 | NULL |
+-----+------------+--------------+
2. Find the rows where greater_time is not only greater but next_time
The best way to filter all of these useless rows is to find out if there are times between time (greater than) and greater_time (lesser than) for this id.
SELECT
i1.id,
i1.time AS time,
i2.time AS next_time,
i3.time AS intrudor_time
FROM
idtimes AS i1
LEFT JOIN idtimes AS i2 ON i1.id = i2.id AND i2.time > i1.time
LEFT JOIN idtimes AS i3 ON i2.id = i3.id AND i3.time > i1.time AND i3.time < i2.time
ops, we still have a false next_time!
+-----+------------+--------------+---------------+
| id | time | next_time | intrudor_time |
+-----+------------+--------------+---------------+
| 155 | 1300000000 | 1311111111 | NULL |
| 155 | 1300000000 | 1322222222 | 1311111111 |
| 155 | 1311111111 | 1322222222 | NULL |
| 155 | 1322222222 | NULL | NULL |
| 156 | 1312345678 | 1318765432 | NULL |
| 156 | 1318765432 | NULL | NULL |
+-----+------------+--------------+---------------+
Just filter the rows where this event happens, adding the WHERE
constraint below
WHERE
i3.time IS NULL
Voilà, we have what we need!
+-----+------------+--------------+---------------+
| id | time | next_time | intrudor_time |
+-----+------------+--------------+---------------+
| 155 | 1300000000 | 1311111111 | NULL |
| 155 | 1311111111 | 1322222222 | NULL |
| 155 | 1322222222 | NULL | NULL |
| 156 | 1312345678 | 1318765432 | NULL |
| 156 | 1318765432 | NULL | NULL |
+-----+------------+--------------+---------------+
I hope that you still need an answer after 4 years!
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 firstr.time
which is greater thanl.time
. This happens with first ordering ther.time
s viaGROUP_CONCAT(r.time ORDER BY r.time)
, the by slicing first token viaSUBSTRING_INDEX
.
Good luck, and, don't expect good performance if this table is large.