MySQL Identify median using Case statement within Limit-CodePudding

I'm trying to write a query within mySQL that will return the medium number of Northern Latitudes (LAT_N) rounded to 4 decimal places. Can anyone tell me where I'm going wrong? I'm not necessarily looking for the perfect solution, just want to understand why I can't use the case function within the limit, or whether I'm even writing the case statement correctly.

Select ROUND(Lat_N, 4)
FROM Station
ORDER BY Lat_N
LIMIT CASE 
        WHEN COUNT(LAT_N) % 2 = 0 
          THEN 2 
        ELSE 1 
    END
Offset FLOOR(COUNT(LAT_N)/2);

CodePudding user response：

According to MySQL Reference Manual:

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...

So, you can use dynamic sql query as the following:

Set @lmt = (Select CASE WHEN COUNT(LAT_N) % 2 = 0 THEN 2 ELSE 1 END From Station);
Set @ofst = (Select FLOOR(COUNT(LAT_N)/2) From Station);
Set @qr = CONCAT('Select ROUND(Lat_N, 4) AS LAT_N FROM Station ORDER BY Lat_N LIMIT ', @lmt, ' OFFSET ', @ofst , ';');
PREPARE STMT FROM @qr;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

Another approach, you may use the RWO_NUMBER() function to simulate the limit and offset functionality as the following:

Set @lmt = (Select CASE WHEN COUNT(LAT_N) % 2 = 0 THEN 2 ELSE 1 END From Station);
Set @ofst = (Select FLOOR(COUNT(LAT_N)/2) From Station);
Select D.LAT_N
From
(
  Select ROUND(Lat_N, 4) AS LAT_N, 
         ROW_NUMBER() Over (Order By LAT_N) AS RN
  From Station
) D
Where D.RN Between @ofst 1 And @ofst @lmt

Check this demo.

Also, you may check this post for more information about how to get the median using SQL.

CodePudding user response：

SELECT ROUND(Lat_N, 4)
FROM Station
ORDER BY Lat_N

you read all rows. There is no aggregation taking place. You select and order all rows. Now in your LIMIT clause you suddenly talk about the aggregation value COUNT(LAT_N), but your result set doesn't contain this value, as you never aggregated your data.

Usually you can evaluate aggregation values in a subquery:

LIMIT CASE WHEN (SELECT COUNT(LAT_N) % 2 FROM Station) = 0 THEN ...

but MySQL does not suppport subqueries in the LIMIT clause yet.

Your query doesn't seem to deal with an even amount of rows either.

Here is the query I'd use instead:

SELECT ROUND(AVG(Lat_N), 4)
FROM
(
  SELECT
    Lat_N,
    ROW_NUMBER() OVER (ORDER BY Lat_N) AS rn,
    COUNT(*) OVER () AS cnt
  FROM Station
) x
WHERE rn BETWEEN FLOOR((cnt   1) / 2) AND CEIL((cnt   1) / 2);

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8fb0f7386d0ccb2a386e82b642b21830