How to select an item, the one below and the one above in MYSQL.

Question

I have a database with ID's that are non-integers like this:

b01
b02
b03
d01
d02
d03
d04
s01
s02
s03
s04
s05

etc. The letters represent the type of product, the numbers the next one in that group.

I'd like to be able to select an ID, say d01, and get b05, d01, d02 back. How do I do this in MYSQL?

Answer 1

Find your target row,

SELECT p.id FROM product WHERE id = 'd01'

and the row above it with no other row between the two.

LEFT JOIN product AS p1 ON p1.id > p.id    -- gets the rows above it

LEFT JOIN       -- gets the rows between the two which needs to not exist
        product AS p1a ON p1a.id > p.id AND p1a.id < p1.id

and similarly for the row below it. (Left as an exercise for the reader.)

In my experience this is also quite efficient.

    SELECT
        p.id, p1.id, p2.id
    FROM
        product AS p
    LEFT JOIN
        product AS p1 ON p1.id > p.id
    LEFT JOIN 
        product AS p1a ON p1a.id > p.id AND p1a.id < p1.id
    LEFT JOIN
        product AS p2 ON p2.id < p.id
    LEFT JOIN 
        product AS p2a ON p2a.id < p.id AND p2a.id > p2.id
    WHERE
        p.id = 'd01'
        AND p1a.id IS NULL
        AND p2a.ID IS NULL

Answer 2

Although not a direct answer to your question I personally wouldn't rely on the natural order, since it may change duo to import/exports and produce side effects not easily understandable by fellow programmers. What about creating an alternate INTEGER index and fire up another query? "WHERE id > ...yourdesiredid ... LIMIT 1"?

Answer 3

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(50) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+

mysql> select * from test;
+------+
| id   |
+------+
| b01  | 
| b02  | 
| b03  | 
| b04  | 
+------+

mysql>  select * from test where id >= 'b02' LIMIT 3;
+------+
| id   |
+------+
| b02  | 
| b03  | 
| b04  | 
+------+

Answer 4

Here is another way to do it using UNIONs. I think this is a little easier to understand and more flexible than the accepted answer. Note that the example assumes the id field is unique, which appears to be the case based on your question.

The SQL query below assumes your table is called demo and has a single unique id field, and the table has been populated with the values you listed in your question.

( SELECT id FROM demo WHERE STRCMP ( 'd01', id ) > 0 ORDER BY id DESC LIMIT 1 )
UNION ( SELECT id FROM demo WHERE id = 'd01' ORDER BY id ) UNION 
( SELECT id FROM demo WHERE STRCMP ( 'd01', id ) < 0 ORDER BY id ASC LIMIT 1 ) 
ORDER BY id

It produces the following result: b03, d01, d02.

This solution is flexible because you can change each of the LIMIT 1 statements to LIMIT N where N is any number. That way you can get the previous 3 rows and the following 6 rows, for example.

Answer 5

What about using a cursor? This would let you traverse the returned set one row at a time. using it with two variables (like "current" and "last"), you could inchworm along the result until you hit your target. Then return the value of "last" (for n-1), your entered target (n), and then traverse / iterate one more time and return the "current" (n+1).