0

e.g. In DB: 1 2 3 4 5 5

I want get: 5 5 4 3

I use this:

select *
from product
where price in(
    select price from product group by price order by price desc limit 3
)
order by price desc

But MySQL response this error

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Any other way to do same thing?

CL So
  • 3,647
  • 10
  • 51
  • 95
  • How old is old? – FanoFN May 17 '19 at 04:19
  • Version is 5.6.43-cll-lve - MySQL Community Server (GPL) – CL So May 17 '19 at 04:20
  • What about using `JOIN`? – FanoFN May 17 '19 at 04:21
  • Why are you even nesting a SELECT statement? – David Brossard May 17 '19 at 04:22
  • 2
    Try something like this: https://www.db-fiddle.com/f/iQvnuV7tipWojk9Eq5ZqDh/1 – Nick May 17 '19 at 04:32
  • It is work, but I don't understand why, why left join same table, and why `count(distinct p2.price) <= 2` will make it work? – CL So May 17 '19 at 05:03
  • 1
    Something like this `SELECT * FROM product INNER JOIN (SELECT price FROM product GROUP BY price ORDER BY price DESC LIMIT 3) b ON product.price=b.price ORDER BY product.price DESC` – FanoFN May 17 '19 at 05:14
  • But it is not work if some record is null, I added `and p1.price is not null`, but still not work. https://www.db-fiddle.com/f/6jnJDQo75tm1phaPcR8Pe7/1 – CL So May 17 '19 at 05:24
  • `SELECT * FROM product INNER JOIN (SELECT price FROM product GROUP BY price ORDER BY price DESC LIMIT 3) b ON product.price=b.price ORDER BY product.price DESC` I think this is better answer, no null problem and easy to understand. – CL So May 17 '19 at 05:26

0 Answers0