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 .

FarheenP · Accepted Answer · 2015-01-23 05:10:04Z

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, 2015 at 5:10

FarheenP

3491 gold badge2 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hardik Gajjar Over a year ago

can i have more specification regarding this Question bcz i stuck in query ??

Gervs · Accepted Answer · 2015-01-22 19:26:32Z

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, 2015 at 19:26

answered Jan 22, 2015 at 17:11

Gervs

1,3979 silver badges8 bronze badges

6 Comments

MaNn Over a year ago

Yes I know. Isn't there any other workaround to it except UNION ?

MaNn Over a year ago

In this case,order by do not work.I get Error "Incorrect usage of UNION and ORDER BY"

Gervs Over a year ago

Just checked the MySQL documentation and there's an example exactly the same way, but maybe it's caused by SELECT *.

MaNn Over a year ago

Can I use join ? I am not so good at JOINs ,If you can help :)

Gervs Over a year ago

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

|

Sandro Eric · Accepted Answer · 2015-01-22 17:56:48Z

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, 2015 at 17:56

answered Jan 22, 2015 at 17:08

Sandro Eric

3422 silver badges9 bronze badges

1 Comment

Barranka Over a year ago

Please edit your answer and include some explanation in it. Code-only answers are sometimes good enough, but code+explanation answers are always better.

Collectives™ on Stack Overflow

MySQL Query add limit in SubQuery

3 Answers 3

1 Comment

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related