How to pick up median value in between 3 records? - SQL

Question

Please assist me, I need to pick 790 since its in middle but not able to figure it out.

id  Value
12  780
123 796
124  790

Thank you in advance

You've been an SO member for 5 years. You should know this is not a high quality post. — dfundako, Sep 20 '18 at 13:26
Please tell us which database you are using (e.g. MySQL, SQL Server, Oracle, Postgres, etc.). — Tim Biegeleisen, Sep 20 '18 at 13:26
For SQL server see: https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server — Magnus, Sep 20 '18 at 13:39
@magnus, I had looked at it already and it did not work for me as I already have the median value. — user2711165, Sep 20 '18 at 17:06

score 0 · Answer 1 · answered Sep 20 '18 at 13:34

This should help to get what you want. Get the average value from your table, then get first record with smallest difference with average value.

SELECT TOP 1 id, value FROM yourTable
ORDER BY ABS(value - (SELECT AVG(value) FROM yourTable))

or using variables, depending on the size of your table

DECLARE @median DECIMAL
SELECT @median = AVG(value) FROM yourTable

SELECT TOP 1 * FROM yourTable 
ORDER BY ABS( value - @median)

1 Answers1