Some of the solution I found that looks very fancy:
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > 0.5 LIMIT 1And I think I will try to simulate how it is done:
Based on the above simulation of a simple data, I think this solution might look fancy but it is not a good solution to find median. For example: if we have a data with 10000 numbers, we might generate a matrix 10000x10000 to solve the median problem. For a large dataset, the process of finding median by this method is very slow.
Here is one of my solution:
set @row_num = 0;
select k.val from
(select @row_num := @row_num + 1 as row_index, p.val from
(select val from data order by val) as p
having row_index = (select (COUNT(*)+1)/2 from data)) as k;create table station(
lat_n float
);
Insert Into station (lat_n)
Values (139.65036520),(48.69788572),(72.33748014),(33.54792701),(75.17993079),(50.24380534),(85.32270304),(98.97071940),(46.38739244),(98.72210937),(103.19952640),(38.58161595),(50.98932987),(102.08883160),(49.75112765),(70.52300953),(73.51580724),(39.93234421),(94.66283665),(116.23219630),(36.45673517),(114.21574130),(27.25445814),(106.44605260),(88.98111013),(133.35212330),(83.27433063),(75.42182000),(32.55838209),(79.89165657),(85.93174158),(104.89642620),(74.04958346),(65.75153490),(39.85947921),(74.04169376),(141.09739700),(117.32096110),(72.41930917),(111.08755960),(126.34679980),(32.76641637),(27.34698627),(41.31187761),(138.10033400),(133.97335130),(125.34315670),(70.68239168),(81.88447769),(53.48858773),(62.10307108),(68.43035344),(60.60716473),(106.05692860),(80.29965735),(47.84182680),(87.70708169),(144.84287730),(37.53287288),(122.23681910),(83.92116818),(135.01652730),(76.39074308),(119.46177920),(55.94680767),(128.22879550),(90.98811028),(108.68681780),(104.82772510),(100.52119130),(75.26293787),(95.00896271),(57.79181464),(29.39052378),(66.65054378),(92.75943510),(136.23101590),(120.28307600),(116.02125920),(47.66145958),(118.73980380),(108.60658700),(96.70474244),(68.82384939),(102.54723860),(75.22358450),(119.56071050),(77.30051697),(28.00318693),(67.33892289),(36.95284720),(86.41004231),(102.04716020),(56.57944083),(104.08944720),(97.72136890),(69.59335022),(30.78888129),(35.29719590),(74.05708403),(95.79113784),(55.49838117),(77.92417249),(112.68447990),(106.81224010),(138.49904560),(47.65762321),(59.86766645),(27.98898068),(81.36073326),(45.82996854),(119.93470430),(144.48915040),(103.25478780),(39.94889028),(50.68988119),(68.29708467),(131.55159120),(38.74146904),(60.41001687),(121.46087080),(81.73572165),(109.20572740),(106.23165860),(99.25831292),(82.24062794),(103.58713980),(108.60877880),(75.57692264),(90.65319960),(136.53852810),(45.89251104),(59.13834287),(38.37033443),(121.71105830),(129.83531080),(80.50000412),(35.61763429),(128.84622340),(59.58257004),(82.71809743),(127.44287710),(58.19417297),(116.12239350),(90.94560988),(80.90971236),(95.48926949),(70.83371719),(92.56220722),(38.85256239),(128.24987240),(72.75896875),(109.66029030),(105.47091170),(54.47132153),(95.95531865),(55.72295385),(25.41948948),(137.54856150),(113.13280790),(41.59710148),(127.23477080),(71.60321313),(133.27631400),(85.92578701),(127.56336350),(45.78566304),(85.76134731),(73.50450967),(25.07352606),(48.40697791),(72.91869963),(128.39823150),(99.82252766),(98.73035759),(113.71573790),(61.30374218),(52.08546427),(132.22798420),(127.43304240),(77.43818081),(65.58421931),(35.68357838),(25.40836031),(40.21366400),(129.24535750),(140.36416880),(67.51641885),(130.20243870),(141.31265860),(135.85106190),(36.51851210),(55.60911109),(94.19750922),(134.54681250),(83.89130493),(95.16264172),(31.80492935),(39.39625803),(105.53347840),(98.62295228),(60.83650360),(44.51203489),(129.66821540),(93.28771946),(93.61747947),(100.45113470),(129.40254830),(125.75731010),(130.06765690),(140.86336070),(86.97524724),(93.84076298),(97.17321584),(115.68333740),(134.09385350),(107.05296960),(82.63795084),(141.38378900),(114.90601730),(96.36953674),(135.86039870),(92.36589225),(65.42078424),(127.35637820),(84.78749012),(59.09336238),(106.61799300),(66.84426322),(70.69211520),(140.47239140),(135.13580620),(119.04698490),(70.17241490),(124.68820360),(144.57831850),(39.43148157),(83.12257286),(57.01243150),(104.56861600),(79.15467169),(73.48100913),(63.31154085),(134.88385210),(79.96440727),(52.42646664),(53.41116218),(68.93334418),(142.85738710),(80.49212450),(114.16315900),(73.96926833),(92.43965299),(82.08611195),(75.41614816),(53.32457298),(47.99772806),(119.98815640),(68.74638928),(54.28636867),(50.63729689),(138.69447700),(64.88877035),(75.37281617),(32.03325626),(100.43267240),(47.50617517),(134.01567710),(35.68710434),(31.08726265),(27.28675141),(42.73630964),(91.19831625),(32.96727204),(106.43245730),(31.77168828),(93.21527074),(44.18858219),(106.73494030),(107.60508210),(42.05019623),(132.90339330),(130.94702840),(124.35962040),(71.77418064),(31.11149635),(79.07193272),(74.51222394),(82.25468043),(59.86867012),(49.99183934),(143.27791020),(30.22167082),(117.16454170),(30.34796858),(85.23318916),(37.24884854),(71.02098012),(62.05282515),(130.50036090),(100.02403820),(41.28342297),(136.47650330),(119.25390690),(141.30130540),(113.24859020),(119.64014260),(137.01930790),(35.25310608),(30.15463898),(138.59840730),(117.39242100),(58.03964248),(112.21638740),(44.25844944),(58.87142971),(75.32104008),(140.29582830),(116.51126800),(129.18665070),(69.65812987),(89.20441335),(35.52107321),(109.86729790),(108.73110620),(61.18883190),(139.92559260),(61.00207775),(121.88651050),(110.20401490),(63.71916114),(47.18550342),(40.77104358),(142.15139360),(66.21395656),(140.00306310),(78.22487634),(80.27133556),(41.78237386),(93.22574810),(48.65218811),(144.12948840),(136.26389180),(51.74018501),(56.15413675),(37.31044167),(82.12446036),(69.80182523),(57.75450094),(126.15978440),(109.63046860),(43.98657665),(109.73806610),(83.49946581),(76.31562733),(81.34223218),(112.51871710),(31.93743733),(102.92007060),(52.33346818),(27.31872893),(49.93703023),(27.21713791),(99.26558164),(90.96161545),(91.03351667),(70.36168327),(45.94376238),(131.23177730),(37.79924488),(139.95763380),(117.39456660),(91.71440440),(110.10195500),(37.44478047),(100.37029570),(118.14503670),(111.10992720),(133.23283030),(28.35665956),(59.17878948),(133.92024720),(96.07908272),(116.95763470),(134.01712780),(86.44332452),(40.20107326),(126.13693900),(29.23891674),(61.84149449),(144.02364780),(71.67037017),(42.98811364),(76.96794304),(106.98270160),(101.97821710),(95.59608144),(116.81971460),(143.29820720),(57.17093106),(40.93632826),(51.70599633),(68.61410998),(130.50683130),(78.89639197),(59.21949077),(126.30066650),(114.19112610),(142.52590700),(110.99353460),(143.10291430),(94.54524237),(87.96807414),(135.02092410),(86.98074065),(111.32504490),(91.63742640),(60.70904494),(75.55442471),(144.24681970),(137.96737430),(70.44312726),(32.57766608),(112.74337710),(136.51052350),(61.60220306),(72.05811052),(31.04603084),(65.09140040),(53.38766607),(91.73825386),(36.27989144),(120.29166660),(60.72683604),(79.60794457),(72.53041491),(136.79685140),(108.03633530),(101.43994170),(87.32065593),(144.65717070),(107.62583170),(93.86729369),(71.24714242),(58.96091486),(144.98906270),(111.23374970),(128.62135500),(59.14046276),(49.02687366),(69.81295092),(93.94092511),(111.09905660),(69.89601052),(67.62838061),(131.90098530),(81.73581393),(70.35829861),(89.39690098),(45.66018469),(53.67245685),(144.25585590),(93.53270561),(132.94950230),(81.30137309),(54.20966949),(79.78277762),(119.27260080),(76.62670866),(29.67885038),(70.74998895),(61.12316996),(46.15263324),(120.37967200),(107.17113820),(38.50488828),(99.32176480),(123.64345250),(59.27754758),(57.99646524),(107.86095130),(46.51401744),(95.35416575),(94.82996881),(131.70396780),(45.00536601),(57.09557313),(30.49981982),(61.83246718),(108.04892330),(105.01976480),(78.15510874),(27.67194236),(113.55053050),(110.41134290),(73.16170647),(111.32443070),(137.66588920),(33.41875149);Just try to run the above insert into for a few times for more fun! So by doing that I have created 26946 rows with so many dupplicated data values.
SELECT x.lat_n from station x, station y
group BY x.lat_n
HAVING SUM(SIGN(1-SIGN(y.LAT_N-x.LAT_N)))/COUNT(*) > .5 LIMIT 1;It took nearly 6 mins to finish.
set @row_num = 0;
select round(k.lat_n,4) from
(select @row_num := @row_num + 1 as row_index, p.lat_n from
(select lat_n from station order by lat_n) as p
having row_index = (select round((COUNT(*)+1)/2) from station)) as k;It takes 42.1ms to generate the same result.