0

I need a query that adjoins a list of data (simply one field of float data from a table) ordered ascending with the same field ordered descending.

For context's sake, I'm practicing my (very) rudimentary and (very) rusty SQL (MySQL, specifically). The larger problem I'm working to solve is this HackerRank question, which is to compute the median of the LAT_N field from the STATION table.

My strategy is to return from a table with the data ordered ascending in the first column (up_LAT_N) and the data ordered descending in the second column (down_LAT_N) the average of the first row that satisfies the condition (cond) that the datum in the ascending ordered field is greater than or equal to the datum in the descending ordered field. This average would be the median.

I've tried the following:

SELECT  up.LAT_N AS up_LAT_N, 
        down.LAT_N AS down_LAT_N,
        CASE 
            WHEN up.LAT_N >= down.LAT_N THEN 1 ELSE 0
        END AS cond
FROM
    (SELECT LAT_N FROM STATION ORDER BY LAT_N) up,
    (SELECT LAT_N FROM STATION ORDER BY LAT_N DESC) down

...but I the ordering doesn't seem to be preserved. I'm thinking I could basically do a 'join' of these two columns, but I'm not sure how to introduce an index to each of these on which to join.

Thanks, in advance!

EDIT: Sorry for the confusion: I wasn't looking for the solution to broader question of calculating the median -- just a little nudge to help me implement my own attempt at solving this problem!

Per48edjes
  • 103
  • 7
  • 1
    Possible duplicate of [Simple way to calculate median with MySQL](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) – Nick Jan 06 '19 at 01:35

2 Answers2

0

there is no meaning to order subquery. If you need visual ordering you need to do it on the outer query.

SELECT  up.LAT_N AS up_LAT_N 
        down.LAT_N AS down_LAT_N
        CASE 
            WHEN up.LAT_N >= down.LAT_N THEN 1 ELSE 0
        END AS cond
FROM STATION up, STATION down
ORDER BY up.LAT_N, down.LATN DESC
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72
0

The larger problem I'm working to solve is this HackerRank question, which is to compute the median of the LAT_N field from the STATION table.

You can compute the median directly with the following query : 

SELECT AVG(dd.lat_n) as median_val
FROM (
SELECT d.lat_n, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM station d, (SELECT @rownum:=0) r
  WHERE d.lat_n is NOT NULL
  -- put some where clause here
  ORDER BY d.lat_n
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) )

Tested in this db fiddle (with an even number of rows, which is the most complex case to handle when it comes to medians).

See this excellent SO post for an explanation of the technique.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Rather than copy/paste (with minor edits) an answer from another question, you should simply flag this question as a duplicate of that one. – Nick Jan 06 '19 at 01:35
  • @Nick : ok, done. The question was not originaly targeted on solving the median computation, but rather on what I imagine was an intermediate step towards that. My answer was meaning to point the SO to a direct solution for his global use case (and I quoted the original link, and tested the solution !). – GMB Jan 06 '19 at 01:39
  • Agreed that the question wasn't specifically targeted at the median, but your answer was, so it's more appropriate to call it a duplicate. But definitely brownie points for testing the solution. :-) – Nick Jan 06 '19 at 01:43
  • Yep, y'all are both correct: I'm wasn't asking for an answer to the median computation...just some direction for the intermediate step. Was hoping to [struggle some more/hopefully learn something along the way and] take it to the finish line from there. :) Appreciate the help nonetheless! – Per48edjes Jan 06 '19 at 03:50