CODEWITHSUNDEEP
sqlsql-server
Shinchan_Shiro
Shinchan_Shiro

Reputation: 93

How to get the middle most record(s) from a group of data in sql

create table #middle
(
A INT,
B INT,
C INT
)

INSERT INTO #middle (A,B,C) VALUES (7,6,2),(1,0,8),(9,12,16),(7, 16, 2),(1,12,8), (9,12,16),(9,12,16),(7, 16, 2),(1,12,8), (9,12,16) 

    ;WITH MIDS
         AS (SELECT *,
                    Row_number()
                      OVER (
                        ORDER BY a, b, c DESC )AS rn
             FROM   #middle)
    SELECT *
    FROM   MIDS
    WHERE  rn <= (SELECT CASE ( Count(*)%2 )
                           WHEN 0 THEN ( Count(*) / 2 ) + 1
                           ELSE ( Count(*) / 2 )
                         END
                  FROM   MIDS) except (SELECT *
                                  FROM   MIDS
                                  WHERE  rn < (SELECT ( Count(*) / 2 )
                                               FROM   MIDS))

The query i have tried works >4 records but not for '3'.Now my question is how should i modify my query so that for 3 records i should get the 2nd record which is the middle most record among them,try to insert only 3 records from above records and help. Thanks in advance.

Upvotes: 2

Views: 3548

Answers (3)

Steve Ford
Steve Ford

Reputation: 7753

How about this:

**EDITED 

;WITH MIDS
     AS (SELECT *,
                Row_number()
                  OVER (
                    ORDER BY a, b, c DESC )AS rn
         FROM   #middle),
Cnt
AS
    (SELECT COUNT(*) c, COUNT(*)%2 as rem, COUNT(*)/2 as mid FROM Mids)
SELECT *
FROM   MIDS
CROSS APPLY cnt
where (rn >= cnt.mid and rn <= cnt.mid + 1 AND cnt.rem = 0) OR
      (cnt.rem <> 0 AND rn = cnt.mid+1)

Upvotes: 2

Aducci
Aducci

Reputation: 26664

You can use OFFSET and FETCH 

select *
from #middle
order by a, b, c desc
offset (select count(*) / 2 - (case when count(*) % 2 = 0 then 1 else 0 end) from #middle) rows
fetch next (select 2 - (count(*) % 2) from #middle) rows only

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269913

There are many ways to get the median in SQL. Here is a simple way:

select h.*
from (select h.*, row_number() over (order by a, b, c desc) as seqnum,
             count(*) over () as cnt
      from #highest h
     ) h
where 2 * rn in (cnt, cnt - 1, cnt + 1);

For an even number of records, you will get two rows. You need to decide what you actually want in this case.

Upvotes: 3

Related Questions