MySQL - select one row - then one next and one previous relative to the selected

Question

I'll try to make this clear.

I need to select a specific row and one row previous relative from that selected row and one row next relative from that selected row without using id's. Is this possible? Previous and next one, in short.

The reason why I can't (maybe I just don't know how) use id's, is because they are not in sequential order. They have gaps as you can see from this rather immature and random example.

TABLE <-the name of the table
+----+----------------------+-------+
| id | name                 | value |
+----+----------------------+-------+
|  1 | some_name            | asf   |
+----+----------------------+-------+
|  4 | hello                | A3r   |
+----+----------------------+-------+
|  5 | how_do_you_do        | HR5   |
+----+----------------------+-------+
|  8 | not_bad              | 00D   |
+----+----------------------+-------+
| 12 | i_like_women         | lla   |
+----+----------------------+-------+
| 13 | are_you_serious      | 1Ha   |
+----+----------------------+-------+
| 15 | nah_i_kid            | Ad4   |
+----+----------------------+-------+
| 17 | it_is_just_the_boobs | Zc5   |
+----+----------------------+-------+
| 18 | thank_god            | 102   |
+----+----------------------+-------+
| 44 | no_kidding           | jjy   |
+----+----------------------+-------+

First, I need to select one row based on specific value from one of its column. I know how to do that:

SELECT `value` 
FROM `TABLE` 
WHERE name = 'i_like_women'

This will select one row with id 12 with the value lla.

What I need is to select another at least two rows: one with the name 'not_bad' and one with the name 'are_you_serious' without specifying it. Or, in other words, previous and next one relative to this selected one.

In short, three rows should be selected based on one value. I'm new to MySQL, as you can guess.

Thanks for your time and attention. Enjoy helping me.

This post can be you http://stackoverflow.com/questions/1813641/select-a-row-and-rows-around-it — Jhonathan, Sep 14 '12 at 19:34

score 9 · Answer 1 · edited May 22 '13 at 21:25

9

Here is the query which will return all three records.

SELECT * 
FROM `TABLE` 
WHERE id >= (
    SELECT id 
    FROM `TABLE` 
    WHERE id < (SELECT id FROM `TABLE` WHERE name = 'i_like_women')
    ORDER BY id DESC 
    LIMIT 1
)
ORDER BY id ASC
LIMIT 3

edited May 22 '13 at 21:25

cimmanon

67,211
17
165
171

answered Sep 14 '12 at 19:45

Ramiz Raja

5,942
3
27
39

2

Depending on what the asker wants, this may not work for the first record; you'll bring 3 back rather than 2, or maybe nothing at all because there is no id < 1. Otherwise, nice approach! – dash Sep 14 '12 at 19:48
@VolosBlur Cool - I did too :-) I'm surprised it works if you feed in 'some_name' though. – dash Sep 14 '12 at 19:57
Well it seems it does return nothing if I replace name = 'i_like_women' with id = 'some_number'. But if I use name = 'i_like_women' it works. – VolosBlur Sep 14 '12 at 20:10
Best answer! Thanks! Very clever! – Nikolay Traykov Aug 06 '15 at 16:48

score 8 · Accepted Answer · answered Sep 14 '12 at 19:29

The simplest way to do this is to exploit the fact that, although not continuous, your ids are in ascending order.

For example:

SELECT * FROM Table WHERE id = 8

UNION
--Select the first item less than 8
SELECT * FROM Table WHERE id = (SELECT MAX(id) FROM Table WHERE id < 8)

UNION
--select the first item greater than 8
SELECT * FROM Table WHERE id = (SELECT MIN(id) FROM Table WHERE id > 8)

If you only know the string, then:

DECLARE _id INT

SELECT _id = id FROM Table WHERE value = 'i_like_women'

Then you can simply feed this _id into the above query, instead of 8.

Note you don't need to use ` to demarcate the table and column names.

Yep. I think this one works. it selects one specific row and two on the "other sides". Thank you very much! — VolosBlur, Sep 14 '12 at 19:40

score 4 · Answer 3 · answered Sep 14 '12 at 19:29

4

The one before can be retrieved with:

SELECT `value` 
FROM `TABLE` 
WHERE id < (SELECT id FROM `TABLE` WHERE name = 'i_like_women')
ORDER BY id DESC
LIMIT 1

You can do the opposite query to find the next one

answered Sep 14 '12 at 19:29

Tchoupi

14,560
5
37
71

This one works too. Can you "UNION" the "opposite query" with this one? – VolosBlur Sep 14 '12 at 19:42
Works for me!! Thank you – Jonathan Nuñez Jul 28 '17 at 22:06

score 1 · Answer 4 · answered Feb 11 '14 at 06:49

this query is working fine on first and last record as well

SELECT * FROM `products` WHERE `ProductId`  = (SELECT MAX(ProductId) FROM `products`  WHERE ProductId < 1)  AND SubCategoryId=1


UNION

SELECT * FROM `products` WHERE `ProductId`  = (SELECT MIN(ProductId) FROM `products`  WHERE ProductId > 1)  AND SubCategoryId=1


UNION

SELECT * FROM `products` WHERE `ProductId`  = (SELECT MIN(ProductId) FROM `products`  WHERE ProductId < 1)  AND SubCategoryId=1 


UNION

SELECT * FROM `products` WHERE `ProductId`  = (SELECT MAX(ProductId) FROM `products`  WHERE ProductId > 1)  AND SubCategoryId=1


ORDER BY ProductId ASC

i Hope this will solve your Problem :)

score 0 · Answer 5 · edited Oct 27 '15 at 13:09

I have found better and easy answer for the below question(finding next and previous row ),

$neighbors = $this->WorkDescription->find('neighbors',array('field' => 'id', 'value' => 3));

it will gives output like this-

Array
(
    [prev] => Array
        (
            [WorkDescription] => Array
                (
                    [id] => 1
                    [title] => Twenty
                    [ter_id] => 1
                    [cat_id] => 4    
                    [writer] => abk
                    [director] => Dir
                    [producer] => pro   
               )
        )

    [next] => Array
        (
            [WorkDescription] => Array
                (
                    [id] => 3
                    [title] => The Piper
                    [ter_id] => 1
                    [cat_id] => 3        
                    [writer] => abk
                    [director] => Dir
                    [producer] => pro
                )
           )
)

MySQL - select one row - then one next and one previous relative to the selected

5 Answers5

Linked