Use row_number() to take middle row in a set of records

Question

previously I opened an issue asking how to take the first row in a set of records which were resolved brilliantly. Pitifully my customer changed his request a little after so this time I'm asking your help to find the middle row in a set of records i.e. having this:

TemperatureID	CastingID	TemperatureDateTime	TemperatureValue
1421294	1073513	2021-01-07 11:53:00.000	1648
1421295	1073513	2021-01-07 11:54:00.000	1698
1421296	1073513	2021-01-07 11:57:00.000	1699

I have to take the 1421295 record, whereas having an even number either the middle + 1 or middle - 1

So starting from this query, which return the 1421294 record to be clear

SELECT * 
    FROM (
        SELECT 
            CastingID, TemperatureValue, TemperatureDateTime
            , ROW_NUMBER() OVER (PARTITION BY CastingID ORDER BY CastingID) RN
        FROM Production.Temperatures
        where YEAR(TemperatureDateTime) = 2021 and PhaseID = 250
    ) A
    WHERE RN = 1 and year(A.TemperatureDateTime) = 2021

can I obtain the result I want or I have to change my approach?

So you want the "median row"? Will there always be 3 rows, or could there be more/less? Does this answer your question? [Function to Calculate Median in SQL Server](https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server) — Thom A, Apr 04 '22 at 15:49
Side note: Don't use syntax like `WHERE YEAR(TemperatureDateTime) = 2021`, it's not SARGable; use proper date boundaries (`WHERE [Date] >= {Start Date} AND [Date] < {End Date}`). Also, there is no point (in the above) rechecking that the date is in the correct range in the outer query if you're already doing so in the `WHERE` of the inner query. — Thom A, Apr 04 '22 at 15:51
Hi, first of all thanks for the tip. To answer your question there could be even 30 rows with same CastingID but I awlays need the median i.e. the 15th or the 16th — Stefano Buzzoni, Apr 05 '22 at 06:23
I tried the solutions proposed in the link you posted @Larnu, but unfortuntely they didn't solve my problem since the guy who opened the issue wanted to calculate the median value between a set and not just retrieve the entire row — Stefano Buzzoni, Apr 05 '22 at 07:46
`PARTITION BY x ORDER BY x` doesn't make sense. *By definition* all rows within each partition share the same value of `x`. Therefore, trying to order them within their partition by that same `x` value defines no sort value. — Damien_The_Unbeliever, Apr 05 '22 at 14:47
@Damien_The_Unbeliever that's true but the OVER function actually requires an ORDER BY, so I chose CastingID even though the TemperatureDateTime column would made more sense — Stefano Buzzoni, Apr 05 '22 at 15:00

Edward Radcliffe · Accepted Answer · 2022-04-05T14:31:41.623

2

Edit: Updated to use a cte. Edit2: Updated to use a join rather than a sub-select to handle multiple castingIds

Since ROW_NUMBER() is going to give us a continuous set of values, why not find MAX(RN), divide by 2 and then round? If the MAX(RN) is odd, you'll get the true median, if it's even you'll get rounded down. There's likely a cleaner way to do this but something like this:

  WITH cte AS (
        SELECT 
            temperatureID 
            ,castingID
            ,temperatureValue
            ,ROW_NUMBER() OVER (PARTITION BY castingID ORDER BY TemperatureDateTime) AS RN
        FROM Temperatures
)
SELECT
    * 
FROM cte AS c
INNER JOIN (
    SELECT
        castingID
        ,CEILING(CONVERT(DECIMAL(7,2),MAX(RN)) / 2) AS med
    FROM cte
    GROUP BY castingID
) AS m ON c.rn = m.med AND c.castingID = m.castingID

Here is a SQL Fiddle with the result of the query: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5b3aebf3ab4ced946c90a435d9edce3c

There's three use cases (all with different castingID).

1.) Odd number of rows

2.) Even number of rows

3.) A single row

edited Apr 05 '22 at 14:31

answered Apr 04 '22 at 16:15

Edward Radcliffe

537
2
11

I do think you cannot use alias `A` in the where clause, see: [DBFIDDLE: Invalid object name 'A'](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=91f7c68eb4709f4fe82ae50ec6099d11) – Luuk Apr 04 '22 at 16:32
True. We can use a CTE though. – Edward Radcliffe Apr 04 '22 at 16:47
Hi @EdwardRadcliffe, thanks for your answer, I tried using your query but SQL Server gives me this error: `Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.` – Stefano Buzzoni Apr 05 '22 at 06:36
I thinks it's because when I have an even number of records it returns me 2 rows, is it correct? – Stefano Buzzoni Apr 05 '22 at 06:38
Stefano - that was an oversight on my part. I've updated the query in the comments to use a join which should handle multiple castingIds – Edward Radcliffe Apr 05 '22 at 14:29
Ush thank you very much, it actually works very well. I'll mark that as the answer – Stefano Buzzoni Apr 05 '22 at 14:57

Use row_number() to take middle row in a set of records

1 Answers1