MySQL: How to write a query that looks for the nearest value by a condition?

Question

MySQL

There is such a table
Quantity |Weight| Price
1 | 0.1 | 180
2 | 0.6 | 200
3 | 1.2 | 250
5 | 2 | 300
how to write a query that will select the row with the LARGEST value of the Quantity column closest to the specified value for example: Quantity = 4 the query should return the string: 5 | 2 | 300 Ps I wrote key points in capital letters to draw attention

Answer 1

>>LARGE closest
where quantity >: requested_quantity
order by quantity limit 1

Answer 2

mysql> select * from test1;
+----+-------+--------+-------+
| id | count | weight | price |
+----+-------+--------+-------+
|  1 |     1 |    0.1 |   100 |
|  2 |     2 |    0.6 |   200 |
|  3 |     3 |    1.2 |   250 |
|  4 |     5 |      2 |   300 |
+----+-------+--------+-------+
4 rows in set (0.00 sec)

mysql> select * from test1 having (count-4)>=0 order by count desc limit 1;
+----+-------+--------+-------+
| id | count | weight | price |
+----+-------+--------+-------+
|  4 |     5 |      2 |   300 |
+----+-------+--------+-------+
1 row in set (0.00 sec)

Answer 3

Then it's like this:

(SELECT * FROM `good` WHERE qty >= 4 ORDER BY qty LIMIT 1)
UNION ALL
(SELECT * FROM `good` WHERE qty < 4 ORDER BY qty DESC LIMIT 1)
LIMIT 1

Thank you for suggesting union all - I already forgot that this is how distinct works.

Answer 4

SELECT * FROM (SELECT * FROM `good` WHERE qty >= 4 ORDER BY qty >= 4, qty LIMIT 1) as g
UNION 
SELECT * FROM (SELECT * FROM `good` WHERE qty < 4 ORDER BY qty < 4, qty DESC LIMIT 1) as g

Always select the first line and it will be the largest nearest from 4. If there is no more, the nearest smaller from 4 will be returned.

Answer 5

Recently solved a similar problem in Access. I had two tables: a table of transactions, which had a column with the time of the transaction, and a table of prices with the columns product, price action start time and price.
It was necessary to determine at what price the transactions were made. Both tables had a lot of rows.
I tried a bunch of options: with subqueries, intermediate tables, etc. But they all took a very long time, since for each row from the deals table it was necessary to execute a subquery with LIMIT 1.
As a result, the fastest solution turned out to be: get two tables, sorting them by time, then in the loop simultaneously bypass both tables and perform the intersection manually.