Select one line, one line above and one line below

Question

I have a table with a value column. I need to select the line chosen by the user (on the front), one line above and one line below, so:

My 'orders' table rows

----------------------------------------------
id   |   price |
----------------------------------------------
1    |  1500.00 | 
2    |  1380.00 |
3    |  1880.00 |
4    |  1900.00 |
5    |  1450.00 |
6    |  1700.00 |

If the person chose: 1450.00, I want to bring 1450.00, 1380.00 and 1500.00. Is it possible to make this MySql select? or will i have to do this in php?

I only have the initial query for now:

SELECT * FROM `orders`
ORDER BY price;

`WHERE price = 1450.00 OR price < 1450.00 OR price > 1450.00 LIMIT 3` Maybe, maybe not... — AbraCadaver, Oct 25 '19 at 01:48
Yep!, Only 3 not exactly 'these' 3 values, but always 3 values, the one selected, one above and one below — Jonas Tolentino, Oct 25 '19 at 02:04

score 1 · Accepted Answer · answered Oct 25 '19 at 02:04

1

You could select max and min price to get row. Let try this code:

SELECT * 
FROM orders A
WHERE A.price = 1450.00
OR A.price = (SELECT MAX(B.price) FROM orders B WHERE B.price < 1450.00)
OR A.price = (SELECT MIN(B.price) FROM orders B WHERE B.price > 1450.00)

answered Oct 25 '19 at 02:04

Au Nguyen

655
4
12

Yes man, this is working perfectly as I expected, the three lines were selected, thanks. – Jonas Tolentino Oct 25 '19 at 02:25

score 0 · Answer 2 · answered Oct 25 '19 at 01:48

One way you could do it is using 2 MySQL statements. Something like:

SELECT * FROM `orders` WHERE `price`>=$user_price ORDER BY `price` ASC LIMIT 2

Which should give you the user's selected price and the row above. Then you can execute

SELECT * FROM `orders` WHERE `price`<$user_price ORDER BY `price` DESC LIMIT 1

Which should give you the row below.

I'm not sure if there's a way to combine the two queries, but if I do find anything I'll update the answer :)

Ed Bangga · Answer 3 · 2019-10-25T02:49:14.197

0

Here's how to get this, use max() on < 1450 and and min() on > 1450

select * from test
where price in 
    (select price from test where price = 1450)
        or price in
            (select max(price) from test where price < 1450)
            or price in
                (select min(price) from test where price > 1450)

instead of using or, use in instead to more cleaner.

select * from test
where price in (1450, 
   (select max(price) from test where price < 1450),           
         (select min(price) from test where price > 1450))

see dbfiddle

edited Oct 25 '19 at 02:49

answered Oct 25 '19 at 02:05

Ed Bangga

12,879
4
16
30

should it be `select max(price) from test where price < 1450`? – catcon Oct 25 '19 at 02:18
@catcon, yes, your right. – Ed Bangga Oct 25 '19 at 02:21

score 0 · Answer 4 · answered Oct 25 '19 at 02:52

If the prices are unique and you want exactly three rows:

(select o.*
 from orders o
 where o.price <= :price
 order by o.price
 limit 2
) union all
(select o.*
 from orders o
 where o.price > :price
 order by o.price desc
 limit 1
);

Your question does not clarify what to do in these cases:

When the price is less than or equal to the minimum price.
When the price is greater than or equal to the maximum price.
When there are duplicate prices.

score 0 · Answer 5 · answered Oct 25 '19 at 03:47

0

I think this query should work

select price 
    from orders
    where price <= (select min(price) from orders where price > 1450.00)
order by price desc
limit 3

answered Oct 25 '19 at 03:47

momo

141
6

Select one line, one line above and one line below

5 Answers5