Home 2018 May SQL Puzzle: Find the Median Value from the Given Number

SQL Puzzle: Find the Median Value from the Given Number

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output and find the median value from the given range of Numbers.

Input Data:

Expected Output:

Create a sample table:

Solution 1:

Solution 2:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: Use Group By, Find MIN MAX unit sold of a MonthSQL Server Interview: How to insert the data into Multiple Tables?
Comments: 3
  1. Marcel Hofman
    March 22, 2019 at 2:25 pm

    –using @@rowcount

    DECLARE @rowcount INT
    SET @rowcount = @@ROWCOUNT
    ; WITH CTE AS
    (
    SELECT *, ROW_NUMBER () OVER (ORDER BY number) n FROM tbl_testmedian
    )
    SELECT * FROM CTE WHERE n = (@rowcount + 1) / 2

    ReplyCancel
  2. Dpan
    April 15, 2019 at 9:12 am

    declare @count int
    select @count=count(*)/2 from #tbl_TestMedian
    ;WITH Ordered AS (
    select ROW_NUMBER() Over (Order By Number) as rno,NUmber from #tbl_TestMedian
    )
    select * from Ordered where rno=@count

    ReplyCancel
  3. koustav modak
    October 30, 2019 at 1:57 pm

    select Number from
    (select row_number() over() as t,
    * from tbl_TestMedian )T1
    where t =(

    select (count(1)+1)/2 from tbl_TestMedian )

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

May 9, 2018 3 Comments SQL Puzzle, , , , , , , , , , ,