CODEWITHSUNDEEP
mysqlsql
Mikołaj
Mikołaj

Reputation: 23

SQL - Get multiple closest value

i want make sql query that will search database to find multiple closest value I have following query to find closest value.

SELECT * FROM table
WHERE price >= (50 * .9) and price <= (50 * 1.1)
order by abs(price - 50) LIMIT 1;

Its working fine, but i want to make that it will search more than one values something like:

SELECT * FROM table
WHERE price >= (50 * .9) and price <= (50 * 1.1) //here i want one result (limit 1)
or price >= (50 * 1.9) and price <= (50 * 2.1) //here i want one result (limit 1)
order by abs(price - 50)

I want for each price limit 1 not find all values. How i can do this?

//edit just found answer.

(select *
  from table
  WHERE price >= (50 * .9) and price <= (50 * 1.1)
  order by abs(price - 50)
  limit 1
) union all
(select *
  from table
  WHERE price >= (50 * 1.9) and price <= (50 * 2.1)
  order by abs(price - 50)
  limit 1
)

Upvotes: 1

Views: 262

Answers (2)

Khoshtarkib
Khoshtarkib

Reputation: 170

do you want this

SELECT * FROM table
WHERE price >= (50 * .9) and price <= (50 * 1.1) //here i want one result    (limit 1)
 union
SELECT * FROM table
WHERE price >= (50 * 1.9) and price <= (50 * 2.1) //here i want one result  (limit 1)
 order by abs(price - 50)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

How about using union all?

(SELECT *
 FROM table
 WHERE price >= (50 * 0.9) and price <= (50 * 1.1)
 ORDER BY ABS(price - 50)
 LIMIT 1
) UNION ALL
(SELECT *
 FROM table
 WHERE price >= (50 * 1.9) and price <= (50 * 2.1)
 ORDER BY ABS(price - 2*50)
 LIMIT 1
) ;

Upvotes: 0

Related Questions