MySql request for similar results at a price

Question

I don't know if the title is ok for this question, but my problem is simple.
I have a list of results, and for simplicity, it contains only idand price:

+----+-------+
| id | price |
+----+-------+
| 11 |   10  |
| 52 |   17  |
| 23 |   45  |
| 24 |   50  |
| 55 |   60  |
| 96 |   70  |
|  7 |   75  |
| 78 |   80  |
| 99 |   100 |
+----+-------+

For a given id / price, I need to find the first 2 records with a lower price and the next 2 with a higher price.

For example, for id = 55 and price = 60, the results are as follows:

+----+-------+
| id | price |
+----+-------+
| 23 |   45  |
| 24 |   50  |

| 96 |   70  |
|  7 |   75  |
+----+-------+

In a rough implementation, this can be obtained, of course, using UNION, for example:

SELECT id, price
FROM MyTable
WHERE price <= 60 AND id != 55
ORDER BY price DESC
LIMIT 0,2

UNION ALL

SELECT id, price
FROM MyTable
WHERE price >= 60 AND id != 55
ORDER BY price ASC
LIMIT 0,2

But given the fact that MyTable is actually a view obtained using a complex query, is there any other way to achieve this?

I even thought that instead of executing the query twice (with UNION) to get all the results in a single query, then use PHP to find 4 results.

Answer 1

:

SELECT id, price FROM (
SELECT @rank:=@rank+1 AS rank, @selectedRank := IF(id = 55, @rank,
@selectedRank), id, price
  FROM ( SELECT id, price FROM MyTable ORDER BY price
  ) t1, (SELECT @rank:=0) t2, (SELECT @selectedRank:=0) t3
) results
WHERE rank != @selectedRank AND rank BETWEEN @selectedRank - 2 AND @selectedRank + 2;

: :

SELECT * FROM test.mytable
WHERE
price >= (SELECT price FROM MyTable
    WHERE price <= 60
    ORDER BY price DESC
    LIMIT 2,1)
 and id != 55
ORDER BY price
LIMIT 4;

:

Answer 2

@David Packer , :

SELECT id, price 
FROM (
  SELECT @rank:=@rank+1 AS rank, @selectedRank := IF(id = 55, @rank, @selectedRank), id, price
  FROM (
    SELECT id, price
    FROM MyTable
    ORDER BY price
  ) t1, (SELECT @rank:=0) t2, (SELECT @selectedRank:=0) t3
) results
WHERE rank != @selectedRank
AND rank BETWEEN @selectedRank - 2 AND @selectedRank + 2;