Getting the previous and next step unique ID based on current step number

Question

Simple question, but I can't seem to find a simple solution:

What is the most efficient way to get both the previous and next step unique HEX ID based on current step number given the below data:

----+-------------+----------------
ID  |   UnqID   | STEPNUM |
----+-------------+----------------
21 |   BcDeF   |        1        |
22 |   GhIjKL   |        2        |
23 |   MnOPq  |        3        |
24 |   RsTuV   |        4        |
25 |   wXyZa   |        5        |
----+-------------+----------------

For example:
Lets assume that the provided step is 3, how could I get the unique HEX ID of both 2 and 4?

Thanks!

EDIT
This is what I'm currently using, but it doesn't seem all that efficient to me??

SELECT
    nxt.stepnum AS NextStep,
    nxt.unqID AS NextUnqID,
    prv.stepnum AS PrevStep,
    prv.unqID AS PrevUnqID
FROM foo w,
(
 SELECT stepnum, unqID FROM foo WHERE stepnum > 3 ORDER BY stepnum LIMIT 1
) AS nxt
,
(
 SELECT stepnum, unqID FROM foo WHERE stepnum < 3 AND ORDER BY stepnum DESC LIMIT 1
) AS prv
WHERE w.stepnum = 3

previous and next based on what? Order of rows in mysql are not guaranteed. see [here](http://stackoverflow.com/a/11130318/1066828) — Fahim Parkar, Jun 22 '12 at 18:30
Are there any gaps between `stepnum`s or are they guaranteed to increase by +1 ? — biziclop, Jun 22 '12 at 18:30
@Fahim - correct, I don't want to use the `ID` to determine the step order, just the `stepnum` column if possible. — NotJustClarkKent, Jun 22 '12 at 18:37
@NotJustClarkKent : how `STEPNUM` can be used? And based on what you want to find previous and next?? See my point is, suppose there would have time in your table and if you would have said, find the id which is next and previous based on time. Hope you got my point... — Fahim Parkar, Jun 22 '12 at 18:41
@Fahim - to simplify things I removed the column that would identify this set of steps as part of a "walkthrough" of sorts (we'll call this `WID`) — NotJustClarkKent, Jun 22 '12 at 18:43

biziclop · Accepted Answer · 2012-06-22T19:38:53.437

2

I modified your query. While its a bit nested, it should consist of quite simple index lookups for the sql engine (in theory...)

SELECT
    nxt.stepnum AS NextStep,
    nxt.unqID   AS NextUnqID,
    prv.stepnum AS PrevStep,
    prv.unqID   AS PrevUnqID

FROM (SELECT NULL) AS dummy -- dummy table to LEFT JOIN to

LEFT JOIN
( SELECT stepnum, unqID FROM foo
  WHERE stepnum = (SELECT MIN(stepnum)
                   FROM foo
                   WHERE stepnum > 5 )
) AS nxt ON true

LEFT JOIN
( SELECT stepnum, unqID FROM foo
  WHERE stepnum = (SELECT MAX(stepnum)
                   FROM foo
                   WHERE stepnum < 5 )
) AS prv ON true

edited Jun 22 '12 at 19:38

answered Jun 22 '12 at 18:36

biziclop

14,466
3
49
65

I like the use of `MAX` and `MIN` -- the order / limit is what I didn't like about my query! I'm assuming that the nested selects is the only way to go for this problem then? – NotJustClarkKent Jun 22 '12 at 18:39
It would be nice if MySQL's dumbass optimizer could recognize such situations, but it probably doesn't. – biziclop Jun 22 '12 at 18:41
1

Fiddle fun, view execution plan (of course it might be different in real world): http://sqlfiddle.com/#!2/b61db/1 – biziclop Jun 22 '12 at 18:44
Ok, thanks biziclop. I like this modification so I'm going to roll with it. Thanks for the help! – NotJustClarkKent Jun 22 '12 at 18:44
Damn, ran into an issue: If there is no prev/next step I'm not getting a result. For example, in your fiddle set the step to `5` – NotJustClarkKent Jun 22 '12 at 18:54

Getting the previous and next step unique ID based on current step number

1 Answers1