0

I would like to do this

First, select latest 2500 users who never login. Second, select package and count number of users in each package.

My SubQuery look like this

select packagename, count(*) from customer where id in 
(select id from customer where lastlogin='0000-00-00 00:00:00' order by ID Desc limit 2500) 
group by packagename;

But I got following error

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I am using version 5.6.11-enterprise-commercial-advanced-log

Any alternative query that I can use wihtout upgrade MySQL ?

squancy
  • 565
  • 1
  • 7
  • 25
user2194507
  • 49
  • 6
  • Have you tried the solution (a `join`, assuming your id is the primary key) provided in [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](https://stackoverflow.com/q/17892762) – Solarflare Jul 12 '18 at 07:42
  • I'm not sure I can use join in this case becuase the main query using group by and not select any ID. So I can't join in subquery. If you can provide sample would be appriciate. – user2194507 Jul 12 '18 at 07:56
  • So you do not want to try it because you do not think it works? (Spoiler alert: it does work. If not, post the code you tried, as you did something wrong). – Solarflare Jul 12 '18 at 08:03
  • I think it work now. select packagename,count(*) from customer as c1 join (select id from customer where lastlogin='0000-00-00 00:00:00' order by ID Desc limit 2500) as c2 on c1.id=c2.id group by packagename; Thank you very much. :-) – user2194507 Jul 12 '18 at 08:12

0 Answers0