MySql query to get similar results based on price

Question

Don't know if the title for this question is ok, but my problem is simple.
I have a list of results and for simplicity, it contains only an id and a 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 first 2 records with a lower price and next 2 with a higher price.

For example, for id = 55 and price = 60, the results would be:

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

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

In a rough implementation, this can be of-course obtained with a UNION, like this:

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 behind, is there another way to achieve this?

I was even thinking that instead of running the query twice (with the UNION), to get all the results in one query and then to use PHP to find the 4 results.

The ids are random. I need the rows with first 2 lowest prices and then next 2 prices. I have edited the example. — CristiC, Nov 29 '15 at 18:02
On the information provided, I think your solution is as good as any. Of course, we don't really know what the 'complex query behind' is, it's hard to comment further — Strawberry, Nov 29 '15 at 18:04
Your solution is fine, CristiC. The databases are meant to do exactly that, operations on data. — Andy, Nov 29 '15 at 20:47
To give you a hint for another approach, look for a way to simulate the `ROW_NUMBER()` function in `mysql` (can be most likely simulated with the `COUNT()` function). If you can acquire the row number in a select for the value you are looking for, you can then simply select the 2 preceeding and subsequent rows and return them as a resultset. — Andy, Nov 29 '15 at 20:56
I think this code not different a lot from your but may be good: `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; ` — Wajih, Nov 29 '15 at 22:28

score 2 · Accepted Answer · answered Nov 30 '15 at 10:35

You have to check this: Your query

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;

And this is what query explain shows: And this is another one:

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;

Which Query explain shows for it:

Although the second query might look simpler, there is a problem with it - it assumes you know the price beforehand. The more complicated approach does not, and you could simply pass an ID into the SQL query and get the desired result. Using the second SQL, in the end you are running two SQL queries, first to get the price of the selected ID, second to get the closest values, which CristiC did not want to do. — Andy, Dec 01 '15 at 09:39

score 0 · Answer 2 · edited May 23 '17 at 12:30

Thanks to @David Packer's hint and to this link, I found the solution to iterate the table only once:

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;

MySql query to get similar results based on price

2 Answers2