Mysql SELECT function inside mysql LIMIT function

Question

SELECT COUNT('2018-02-12') 
as ids FROM tg_partner_data
WHERE assign_to = '2'
AND
followup_date = '2018-02-12' AND active='Y'
LIMIT (SELECT count FROM tg_master_count WHERE count_for = 'followup')

I tried to get count as output but this query doesnot works . I am using like this because the limit setting is dynamic.

dev.mysql.com/doc/refman/5.7/en/select.html says: "LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants" You can also use parameter placeholders or integer variables, but not subqueries or other syntax. — Bill Karwin
– Bill Karwin, Commented Feb 12, 2018 at 6:16

Tim Biegeleisen · Accepted Answer · 2018-02-12 06:20:51Z

2

An immediate problem with your use of LIMIT is that you have no ORDER BY clause. Which first records do you expect from the result set? This is not clear. I will give a generic pattern which you can use to have a subquery limit the number of records.

Consider a simple table with just one column:

col
1
2
3
4
5

Now let's say that you want to limit the number of records in the result set, based on an ordering of col, using some subquery. We can write the following:

SET @rn=0;

SELECT col
FROM
(
    SELECT
        col,
        @rn:=@rn+1 rn
    FROM yourTable
    ORDER BY
        col
) t
WHERE t.rn <= (SELECT 3 FROM dual);    -- replace with your own subquery

Demo

The basic idea here is that we simulate row number in a subquery, that is, we assign a row number to each record, ordered according the the col column. Then we subquery that table, and retain only records matching the subquery you want to use to limit.

edited Feb 12, 2018 at 6:20

answered Feb 12, 2018 at 6:14

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

2 Comments

sooraj s pillai Over a year ago

count is a field name.

Tim Biegeleisen Over a year ago

@soorajspillai Your query is in a mess, with many problems. I'm going to update with a generic pattern you can use. 2 minutes please.

Collectives™ on Stack Overflow

Mysql SELECT function inside mysql LIMIT function

1 Answer 1

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related