Mysql Question posted in
The official MySQL documentation can be found here.

Does OFFSET syntax in MySQL require a number parameter? Could it be more like a variable?

I am doing this HackerRank SQL question and cannot understand why this OFFSET does not work.This is for MySQL syntax:

SELECT ROUND(LAT_N, 4)
FROM STATION
ORDER BY LAT_N
LIMIT 1 OFFSET (SELECT FLOOR(COUNT(*) / 2) FROM STATION);

But if I manually calculate the number of rows to skip, get the number, and put that number after OFFSET, it works perfectly. But it’s not dynamic. Do you know why?

Tags:

2

Answers


  1. 0

    The limit and offset must be constants. From the documentation:

    The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

    • Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
    • Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.
    Login or Signup to reply.
  2. 0

    You can use a prepared statement:

    select count(*) div 2 into @offset from STATION;
prepare stmt from 
    "select ROUND(LAT_N, 4) from STATION order by LAT_N LIMIT 1 OFFSET ?";
execute stmt using @offset;

    Here I use integer arithmetic count(*) div 2 instead of float arithmetic FLOOR(COUNT(*)/2)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search