Timeline for Function to Calculate Median in SQL Server
Current License: CC BY-SA 4.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jan 18, 2021 at 14:06 | comment | added | Rudey | The "2019 UPDATE" is misleading, because the query and linked resource do not cover grouped medians, while the original answer does. You can't compare these queries directly. | |
| Dec 16, 2019 at 15:10 | comment | added | kiradotee |
Got stuck with this. Perfect answer (the original version) but I had a lot of same numbers and no SalesId, or any other unique ID. I found adding this to the original dataset (if you imagine Sales.SalesOrderHeader being a view or a WITH query and not a table) solves the issue (as we're just adding a fake unique ID): NEWID() AS disambiguator.
|
|
| S Nov 20, 2019 at 22:57 | history | edited | Justin Grant | CC BY-SA 4.0 |
Updated with SQL 2012+ guidance
|
| Nov 20, 2019 at 18:44 | review | Suggested edits | |||
| S Nov 20, 2019 at 22:57 | |||||
| Feb 18, 2019 at 16:11 | history | edited | Luke Girvin | CC BY-SA 4.0 |
edited body
|
| Aug 2, 2018 at 18:16 | comment | added | BallpointBen |
Without a disambiguator, would it be possible to get a count of each value (count+groupby), then check that rowAsc lies in the range [rowDesc - valueCount / 2, rowDesc + valueCount / 2]?
|
|
| Feb 14, 2018 at 18:43 | history | edited | Justin Grant | CC BY-SA 3.0 |
added 76 characters in body
|
| Dec 13, 2017 at 0:02 | history | edited | Peter Mortensen | CC BY-SA 3.0 |
Active reading. [<https://en.wiktionary.org/wiki/particularly#Adverb>] Dressed the naked link. Updated the link.
|
| Jun 2, 2017 at 21:48 | history | edited | Justin Grant | CC BY-SA 3.0 |
fixed typo
|
| Dec 19, 2013 at 15:56 | comment | added | CodeMonkey | Awesome! long have i known its importance but now i can give it a name... the disambiguator! Thank you Justin! | |
| May 23, 2012 at 14:27 | comment | added | hoffmanc | I suggest adding a comment to the code itself, describing the need for the disambiguator. | |
| Oct 10, 2010 at 3:10 | comment | added | Jonathan Beerhalter | Thanks, when switching the columns to my DB, I dropped the disambiguator, thinking it wasn't relevant. In that case, this solution works really really well. | |
| Oct 6, 2010 at 18:24 | comment | added | Justin Grant | That's why having a disambiguator (SalesOrderId in the code example above) is important, so you can ensure that the order of result-set rows is consistent both backwards and forwards. Often a unique primary key makes an ideal disambiguator because it's available without a separate index lookup. If there's no disambiguation column available (for example, if the table has no uniquifying key), then another approach must be used to calculate median, because as you correctly point out, if you can't guarantee that DESC row numbers are mirror images of ASC row numbers, then results are unpredictable. | |
| Oct 5, 2010 at 23:58 | comment | added | Jonathan Beerhalter | I don't think this works if you have dupes, particularly a lot of dupes, in your data. You can't guarantee the row_numbers will line up. You can get some really crazy answers for your median, or even worse, no median at all. | |
| Apr 15, 2010 at 7:19 | history | edited | Justin Grant | CC BY-SA 2.5 |
added 252 characters in body
|
| Nov 9, 2009 at 9:13 | vote | accept | Yaakov Ellis | ||
| Oct 14, 2009 at 17:54 | history | answered | Justin Grant | CC BY-SA 2.5 |