How to diagnose SQL errors?

Question

I'm self-learning SQL and after finishing the basics in SQLbolt, I discovered HackerRank and got stuck with this problem suing MySQL.

I tried the following code below, but it returns an error:

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

The error is as follows:

ERROR 1064 (42000) at line 1: 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 '(SELECT FLOOR(COUNT(LAT_N)/2) FROM STATION)' at line 3

What seems to be the problem with the code above? The flow of thought supposedly was to sort the LAT_N in descending manner and get only one value using LIMIT. Now, to get the median, we offset the limit by (SELECT FLOOR(COUNT(LAT_N)/2) FROM STATION). When I only run that query, it returns a value of 249. When I use OFFSET 249 instead of the subquery, the result is correct. Why does it not work with the subquery?

Also, is there a diagnosis tool in SQL that runs you through the step-by-step process of the code, so you would pinpoint on what step the code is returning an error?

Does this answer your question? [How to make limit offset dynamic using only (My)SQL](https://stackoverflow.com/questions/5872667/how-to-make-limit-offset-dynamic-using-only-mysql) — Stu, Nov 06 '21 at 10:27

score 1 · Answer 1 · answered Nov 06 '21 at 10:26

It is not allowed to use a sub-query for the values after LIMIT or OFFSET.

A (clumsy) workaround is shown in the docs using prepared statements.

Something like:

SET @a = (SELECT FLOOR(COUNT(LAT_N)/2) FROM STATION);

PREPARE STMT FROM '
   SELECT ROUND(LAT_N,4) FROM STATION
   ORDER BY LAT_N DESC
   LIMIT 1 OFFSET ?';

EXECUTE STMT USING @a;

How to diagnose SQL errors?

1 Answers1