Finding median with a group by (SQL 2000)
-
I have a table carrying two columns - lets say id and age.
I need to find the median age for each id.
create table getmedian (
id int,
age int)
insert into getmedian values (1, 1)
insert into getmedian values (1, 2)
insert into getmedian values (1, 3)
insert into getmedian values (1, 4)
insert into getmedian values (2, 3)
insert into getmedian values (2, 5)
insert into getmedian values (2, 7)
insert into getmedian values (2, 9)
insert into getmedian values (2, 11)
insert into getmedian values (3, 2)
insert into getmedian values (3, 4)
insert into getmedian values (3, 8)
insert into getmedian values (3, 8)
insert into getmedian values (3, 9)
A quick respocse will be appreciated.
Thanks
-
Lots of ways of doing this
WITH CTE AS (SELECT id,
age,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY age ASC) AS RowAsc,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY age DESC) AS RowDesc
FROM getmedian)
SELECT id,
AVG(age)
FROM CTE
WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY id
ORDER BY id;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Will this work on SQLSERVER 2000?
-
SQLMAIN (4/13/2012)
Will this work on SQLSERVER 2000?Oops, my bad. Needs SQL Server 2005 or above.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply