Your required solution may not be quite as complicated as the one linked to. That particular poster needed the median of some not-yet-calculated-values so these values are calculated in derived tables.
If you simply have a table with several values that you want to find the median of then the SQL is simpler.
Median does not have a set based solution. You have to iterate in order to find the median (order matters to compute a median). Engines that process a row at a time do medians easily, engines that process sets have a real problem with it.
okay, which is it? "does not have a set based solution" or "have a real problem with it"?
what about this type of solution:
CASE WHEN COUNT(*)%2=1
ELSE (x.Hours+MIN(CASE WHEN y.Hours>x.Hours
FROM BulbLife x, BulbLife y
GROUP BY x.Hours
SUM(CASE WHEN y.Hours <= x.Hours
THEN 1 ELSE 0 END)>=(count(*)+1)/2 AND
SUM(CASE WHEN y.Hours >= x.Hours
THEN 1 ELSE 0 END)>=(count(*)/2)+1
-- from Transact-SQL Cookbook Chapter 8 Statistics in SQL
The Median depends on order in every case, and non-deterministic rows for data with an even number of elements. Because Median depends on element order, there can't be any set based solution, because sets have no order.
I never said that you can't solve it with SQL, that's a very different claim. There are several ways to do that. You still can't solve it declaratively, and you can't solve it with sets.
sets may have no order, but order can still be imposed with predicates
i ask you, are you familiar with the query to rank result rows? it's a theta self-join, and what it does is count the rows that are greater (or lesser) than, based on the value of a column, then adds 1 to get the rank
same thing with partitioning a single set into two -- those values that are higher, and those values that are lower
On the surface, I would call it a performance problem ;-).
okay, i see the smiley, so i won't respond the way i would've if it weren't there, which would've been something along the lines of "oh, so for challenging queries, you dump the data and do it in excel, eh?"
btw, there is no join in the HAVING clause (if you are referring to post #5)