Median in MS Access

[Piotr_M]

thread701-1707625

Hello everyone!
I found that closed thread with solution on making median calculation using MS Access.
It's a brilliant conception there, but leads to performance disaster, when we have little more records in data set (100k and more for example).
I redesigned the query, adding nested level and replacing functions Min and Max with Last, which totally improved performance. Just wanted to share. Cheers!
P.S.
The closed thread was focused on developing the concept into VBA function. I focused on conception itself, showing SQL only.



Originally posted query:

Select
Max(Max(X.ClosingPrice) + Min(Y.closingPrice))/2 AS Median
from
(Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice) AS X,
(Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice DESC) as Y


...and after my improvement:

Select
(X.closingPrice_1 + Y.closingPrice_2)/2 AS Median
from
(SELECT LAST(closingPrice) AS closingPrice_1 FROM (Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice)) AS X,
(SELECT LAST(closingPrice) AS closingPrice_2 FROM (Select TOP 50 PERCENT closingPrice from tblHomes ORDER BY ClosingPrice DESC)) as Y

 

[Andrzejek]

Thanks for sharing and welcome to TT

Pozdrowionka...


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

Cheers/Twoje zdrowie

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[combo]

You can also check the performance of custom DMedian VBA function that extends standard access domain aggregation functions, by Roger Carlson:

http://rogersaccessblog.blogspot.com/2011/10/how-do-i-calculate-median-in-access_10.html

Powodzenia!

combo