Problem with LIMIT & IN/ALL/ANY/SOME subquery

Question

I have this query:

SELECT count(cp.CxID) as intSmokers 
FROM CustPrimarySmoking cp 
JOIN Customer c ON cp.CxID = c.CustomerID 
WHERE 
cp.CxID IN (SELECT CxID FROM CustPrimarySmoking WHERE CxID = cp.CxID LIMIT 1, 9999)

The idea being that the count will be based on the results of the nested query which retrieves all the records for that customer EXCEPT the first record.

HOWEVER, I get this error, which I think is pretty terminal:

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

Does anyone know of any other way of doing this?

Thanks

my dear ... `SELECT count(cp.CxID) as intSmokers FROM CustPrimarySmoking cp JOIN Customer c ON cp.CxID = c.CustomerID WHERE cp.CxID IN (SELECT CxID FROM CustPrimarySmoking WHERE CxID = cp.CxID) LIMIT 1, 9999999` — ajreal, Feb 15 '11 at 13:21
rewrite your query MySQL does not support `LIMIT` in subquery. See http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html — Nishant, Feb 15 '11 at 13:21
AjReal, that won't work; you're trying to limit with an offset of 1 the whole query which is only retrieving one result (the count). — TheBounder, Feb 15 '11 at 13:25
Nishant, that's what I'm asking!!!! Does anyone have any ideas how to rewrite the query?!? — TheBounder, Feb 15 '11 at 13:26
In your CustPrimarySmoking table, it obviously appears to have more than one record per customer (CxID)... Is there another column on the table for uniquness, like an autoincrement column? — DRapp, Feb 15 '11 at 14:19

score 33 · Accepted Answer · answered Feb 15 '11 at 15:08

33

This is how you need to proceed. See the example that I've worked out.

mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
|    4 | name4 |
+------+-------+
4 rows in set (0.00 sec)

mysql> select * from test1;
+------+------+--------+
| id   | tid  | name2  |
+------+------+--------+
|    1 |    2 | name11 |
|    2 |    3 | name12 |
|    3 |    4 | name13 |
+------+------+--------+
3 rows in set (0.00 sec)

mysql> select
    ->  t1.name
    -> from
    ->  test t1
    -> join
    ->  test1 t2 on t2.tid = t1.id
    -> join
    ->  (select id from test where id <4 limit 3) as tempt on tempt.id = t1.id;
+-------+
| name  |
+-------+
| name2 |
| name3 |
+-------+
2 rows in set (0.00 sec)

Hope this helps.

answered Feb 15 '11 at 15:08

Nishant

54,584
13
112
127

I love this solution. Improved by query from 240s to 0.03s. But how come that subquery doesn't give the "doesn't support LIMIT subquery" error? – Simon Forsberg Jul 09 '12 at 19:07
Excellent, this solve my issue & improve the speed of the query !! – Dax Oct 06 '12 at 09:51
Nice solution. There's always more than one way to skin a cat with SQL. – Jon Hulka Nov 30 '12 at 23:22

score 1 · Answer 2 · answered Feb 15 '11 at 13:26

1

You don't need to use the subquery to retrieve all the records, just exclude the first one:

SELECT count(cp.CxID) as intSmokers FROM CustPrimarySmoking cp JOIN Customer c ON cp.CxID = c.CustomerID WHERE cp.CxID > (SELECT cxID FROM CustPrimarySmoking ORDER BY cxID LIMIT 1)

Assuming that cxid is numeric

answered Feb 15 '11 at 13:26

Nikoloff

4,050
1
17
19

That just ignores the very first; not the first for each customer. – TheBounder Feb 15 '11 at 13:30
Then add the join by customers to the subquery too: `WHERE cp.CxID = (SELECT cps.cxID FROM CustPrimarySmoking AS cps JOIN Customer cust ON cps.CxID = cust.CustomerID ORDER BY cps.cxID LIMIT 1)` – Nikoloff Feb 15 '11 at 13:34
That's going to select only the first record for each customer; I want to ignore the first record and select the subsequent ones. – TheBounder Feb 15 '11 at 13:39
It's a typo in the last comment. Sorry about that. I meant change only the subquery, but have replaced the > with =. Leave it `WHERE cp.CxID >` as it was in the original query and that should select all records for each customer, except for the first one – Nikoloff Feb 15 '11 at 13:41

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

You can also double-nest the inner query to get around this restriction, see:

Mysql delete statement with limit

edited May 23 '17 at 12:17

Community

1
1

answered Feb 12 '16 at 00:16

Dobes Vandermeer

8,463
5
43
46

score 0 · Answer 4 · answered Nov 08 '17 at 23:53

This limitation is a pain if you want to get something like "top N rows for each group". But in your case I wouldn't use that feature even if it were possible. What you try to do is to count all rows except of one row each CxID. All you need is just to subtract the number of distinct CustomerIDs, which is count(DISTINCT cp.CxID). So your final query should be as simple as:

SELECT count(cp.CxID) - count(DISTINCT cp.CxID) as intSmokers 
FROM CustPrimarySmoking cp

Problem with LIMIT & IN/ALL/ANY/SOME subquery

4 Answers4

Linked

Related