How to use a dynamic limit in sql query

Question

I have a query SELECT * FROM grades WHERE userid = 4123;

I want to limit this query

I have a query SELECT * FROM grades WHERE userid = 4123 LIMIT(2);

This works great but if I want this limit to be dynamic from another query.

SELECT COUNT(id) FROM count_table WHERE course = 131;

doing this gives me a syntax error

SELECT * FROM grades WHERE userid = 4123 LIMIT (SELECT COUNT(id) FROM count_table WHERE course = 131);

if this is not possible at all, then is there an alternative way to achieve this?

please help.!

'LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, ' - dev.mysql.com/doc/refman/8.0/en/select.html. Try dynamic sql. — P.Salmon
– P.Salmon, Commented Jun 21, 2019 at 11:57
Possible duplicate of How to write a SQL query with dynamic LIMIT — George Pant
– George Pant, Commented Jun 21, 2019 at 12:32
By the way, using LIMIT without ORDER BY makes little sense. — The Impaler
– The Impaler, Commented Jun 21, 2019 at 12:39

The Impaler · Accepted Answer · 2019-06-21 12:42:52Z

0

You can do it in MySQL 8.x using the ROW_NUMBER() function.

Assuming you order the rows by some column (I guessed the column ID... change it as needed), you can do:

select
  g.*
from (
  select
    *,
    row_number() over(order by id) as rn -- change ordering as needed
  from grades
  ) g
join (
  SELECT COUNT(id) as cnt FROM count_table WHERE course = 131
) c on g.rn <= c.cnt

answered Jun 21, 2019 at 12:42

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

2 Comments

Punit Over a year ago

sql <e>Query: select g.* from ( select *, row_number() over(order by id) as rn from grades ) g join ( SELECT COUNT(id) as cn...  Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(order by id) as rn    from grades   ) g join (   SELECT C' at line 6

The Impaler Over a year ago

Check you MySQL version. It seems you don't have 8.x yet.

Collectives™ on Stack Overflow

How to use a dynamic limit in sql query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related