Finding Median in SQL Server in first 10 rows

Question

SELECT
    [RowAsc] AS RowAsc,
    [RowDesc] AS RowDesc,
    UnitRate 
FROM
    (SELECT TOP(10)
         JobName AS JobName, Client AS Client,
         AssetClass AS AssetClass, AssetType AS AssetType,    
         AssetSubType AS AssetSubType,
         Component AS Component, 
         ComponentType AS ComponentType, ComponentSubType AS ComponentSubType,
         UnitRate AS UnitRate,
         ROW_NUMBER() OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType 
                            ORDER BY UnitRate ASC) AS [RowAsc],
         ROW_NUMBER() OVER (PARTITION BY JobName, Client, AssetClass, AssetType, AssetSubType, Component, ComponentType, ComponentSubType 
                            ORDER BY UnitRate DESC) AS [RowDesc]
     FROM 
         [dbo].[ReplaceCost_DirectCost_Details] rdd
     WHERE
         client = 'APV_Ballina_Shire_Council_Old'  
         AND UnitRate IS NOT NULL
         AND UnitRate <> 0) x
WHERE    
    RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

I have this script to get the median of UnitRate but it gives me wrong result for median.I have 10 rows and want to get the median of UnitRate for those 10 rows. Thanks in advance for your help.

141 325 325 353 3166 325 207 141 3166 161, these are my values for UnitRate.So it gives me row 5 & 6 and value for median should be 325 and 325 but it gives me 5000. — Maryam Yasaei, Nov 21 '17 at 01:41
`first 10 rows` in terms of what sequence ? I see a `top (10)` but didn't see any `order by` in your inner query — Squirrel, Nov 21 '17 at 02:09
@Squirrel , I order by Unitrate in partition, is that what did you mean ? — Maryam Yasaei, Nov 21 '17 at 02:12
i mean you have `top (10) ` but without any `order by` in the inner query. You wanted `first 10 rows`. How do you determine which are the first 10 rows ? based on what sequence (column) ? — Squirrel, Nov 21 '17 at 02:15
yes, I want first then top , when I said top (10) it does not select 10 first?@Squirrel — Maryam Yasaei, Nov 21 '17 at 02:17
have you run the inner query by itself and examined its results to make sure it is what you expected? Like @Squirrel says, you are selecting top 10 without any order, so who knows what rows and values for RowAsc and RowDesc you are getting. — , Nov 21 '17 at 02:27
yes. TOP 10 based on what sequence ? the UnitRate ? AssetID ? Date ? you need to specify in the ORDER BY or else the sequence is not guaranteed — Squirrel, Nov 21 '17 at 02:27
those columns I got after select { jobname, client , AssetClass, Assettype, ...} @Squirrel — Maryam Yasaei, Nov 21 '17 at 02:29
isn't this issue already dealt with in your other thread https://stackoverflow.com/questions/47340987/finding-median-in-sql-server ? — Squirrel, Nov 21 '17 at 03:15
For us to help you better, you need to post the table schema, sample data and expected result. This statement `"141 325 325 353 3166 325 207 141 3166 161, these are my values for UnitRate.So it gives me row 5 & 6 and value for median should be 325 and 325 but it gives me 5000"` does not help us to understand your scenario at all. — Squirrel, Nov 21 '17 at 04:39
@Squirrel thanks, yes , sure, I do that , but please give me time. Many thanks. — Maryam Yasaei, Nov 21 '17 at 04:41
The accepted answer [here](https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server) is similar to your algorithm. It takes the Average (in your case UnitRate) in the outer query. So just: `AVG(UnitRate)`. — Zorkolot, Nov 21 '17 at 15:37

score 1 · Answer 1 · answered Nov 21 '17 at 03:19

1

For an even number of rows, you need a way to return one row. One method is avg():

Select avg(UnitRate)
from . . .

This is all that is needed in the outer query.

answered Nov 21 '17 at 03:19

Gordon Linoff

1,122,135
50
484
624

score 0 · Answer 2 · 2017-11-21T02:37:57.563

Run the inner query by itself, and modify it until you get the correct results (I was going to say the results you expect, but that is not necessarily correct). Allow the process of refining that query to show you if your expectations are correct or not. Run it with the top 10 constraint and without it to see what that is doing, and possibly add an order by clause that will get you the right top 10. Once you have that inner query running appropriately and giving you the correct results, add the outer query back in.

My suspicion is not so much that you are missing an order by, but that you have put the top 10 in the inner query instead of the outer query. You might also try moving that top 10 to the outer query, if all you want is the first 10 unit rate medians over the window attributes you have specified.

Without a better understanding of your schema and what you are trying to get at, it is impossible to give you a precise answer, so I am settling for giving you a methodology as an answer... but I am left wondering if this should be a comment.

Finding Median in SQL Server in first 10 rows

2 Answers2