This is how you can get the median value of a column in PostgreSql. Thanks to http://old.nabble.com/SELECT-TOP–d-PERCENT,-or-SELECT-…-LIMIT–d-PERCENT—td19941366.html
WITH c AS (select count(*)/2 as n from FOO) select COL from FOO order by COL asc limit (1) offset (select n from c);
It is much simpler if you are using SQL Server as described in: http://www.sqlmag.com/Article/ArticleID/49827/sql_server_49827.html
SELECT MAX(Value) FROM (SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1; -- Reza++
Hello,
a) Your query doesn’t search median! Go to wikipedia and look on median definition.
look on http://www.depesz.com/index.php/2009/07/13/calculating-median/
Regards
Pavel Stehule
Comment by Pavel Stehule — November 19, 2009 @ 10:50 am
Thanks Pavel.
Yes! It will not, as I’m not considering the median in an even number of rows. But, for my application that there are several thousands rows in the database, one row difference is not really a big deal. The query I wrote is not really precise.
Is there any other problem that you think the query has and I have not noticed?
Comment by plusplus — November 19, 2009 @ 11:12 am