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

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开发者_Go百科

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.

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

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

Mysql delete statement with limit

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