MySQL Query add limit in SubQuery

Question

Here is my table test with values:

Price
----------
300
600
900
1000
1800
2000

I want to query such that when I search for 300 ,I should get 4 records 300,600,900,1000.

If I search for 900, I can get 600,900,1000,1800. i.e. Two records <=900 and Two record >900

Here is the query I tried :

SELECT * FROM table h where CONDITIONS 
 and  (price in (select price from table where price <=900) // I want to add   LIMIT 2 in this subquery
 or price in (select price from table where price >900)//LIMIT 2
)
order by FIELD(price ,900) DESC limit 5;

I searched a lot on stack overflow,but nothing worked. Please help .

score 5 · Accepted Answer · answered Jan 23 '15 at 05:10

5

You can try the following...

select * from ((select h.* from table_name h where amount <=300 order by amount desc limit 2)
union
(select h.* from table_name h where amount >300 order by amount limit 2)) 
derived_table order by FIELD(amount,300) desc;

answered Jan 23 '15 at 05:10

FarheenP

349
1
2
11

1

can i have more specification regarding this Question bcz i stuck in query ?? – Hardik Gajjar Jun 08 '15 at 06:31

Gervs · Answer 2 · 2015-01-22T19:26:32.607

2

MySQL doesn't support LIMIT in WHERE IN/EXSISTS/ANY/SOME subqueries, you can do this with UNION

(SELECT * /* this should be a columnlist */
 FROM tablename
 WHERE price < 900
 ORDER BY price LIMIT 2)

 UNION

(SELECT * /* this should be a columnlist */
 FROM tablename
 WHERE price >= 900
 ORDER BY price LIMIT 2)

The parenthesis around each select are crucial.

See SQL Fiddle

edited Jan 22 '15 at 19:26

answered Jan 22 '15 at 17:11

Gervs

1,397
9
8

Yes I know. Isn't there any other workaround to it except UNION ? – MaNn Jan 22 '15 at 17:20
In this case,order by do not work.I get Error "Incorrect usage of UNION and ORDER BY" – MaNn Jan 22 '15 at 17:55
Just checked the MySQL documentation and there's an example exactly the same way, but maybe it's caused by SELECT *. – Gervs Jan 22 '15 at 18:14
Can I use join ? I am not so good at JOINs ,If you can help :) – MaNn Jan 22 '15 at 18:32
1

Yes you can, but why should you. Its not less inefficient then union. I've added a link to a fiddle in my answer, and there are no errors – Gervs Jan 22 '15 at 19:25
Can you advice me how can I execute above query using JPQL? – MaNn Mar 03 '15 at 06:57

Sandro Eric · Answer 3 · 2015-01-22T17:56:48.930

0

Using union because this mysql version don't accept limit in subqueries

select * from table where price <=900 limit 2 union select * from table where price > 900 limit 2

edited Jan 22 '15 at 17:56

answered Jan 22 '15 at 17:08

Sandro Eric

332
2
9

Please edit your answer and include some explanation in it. Code-only answers are sometimes good enough, but code+explanation answers are always better. – Barranka Jan 22 '15 at 17:42

MySQL Query add limit in SubQuery

3 Answers3