Geek asks and answers

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

I will try to make it clear.

I need to select a specific row and one row of the previous sibling from this selected row and one row of the next sibling from this selected row without using an identifier. Is it possible? Below and below, in short.

The reason I can't (maybe just don't know how) to use identifiers 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 a specific value from one of its columns. I know how to do this:

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

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

: "not_bad" "are_you_serious" . , , .

, . , MySQL.

. , .

+5
5

- , , , .

:

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)

, :

DECLARE _id INT

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

_id 8.

, `, .

+7

, .

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
+9

One that can be restored before using:

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

You can make the opposite request to find the next

+4
source

this query works fine on both the first and last record

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

Hope this solves your problem :)

+1
source

I found a better and simple answer for the next question (search for the next and previous line),

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

he will give such a conclusion -

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
                )
           )
)
+1
source

More articles:


All Articles