Recently, I will write a database application to get the median value
according to the grouped condition.

maybe, just like the following.

SELECT Max(a1) MaxValue, Median(a1) MedianValue FROM test_table

Any suggestion?

Hi

Check out this previous post:

http://tinyurl.com/q59j

John

"William Jiang" <williamj@qd.lucent.com> wrote in message
news:bm0gon$i2h@netnews.proxy.lucent.com...
> Recently, I will write a database application to get the median value
> according to the grouped condition.
>
> maybe, just like the following.
>
> SELECT Max(a1) MaxValue, Median(a1) MedianValue FROM test_table
>
> Any suggestion?
>
>

A Google search of this group will give you several alternative median
functions depending on the exact form of median you want: Here's one
example:

CREATE TABLE SomeValues (keyx CHAR(1) PRIMARY KEY, valuex INTEGER NOT NULL)

INSERT INTO SomeValues VALUES ('A',1)
INSERT INTO SomeValues VALUES ('B',2)
INSERT INTO SomeValues VALUES ('C',3)
INSERT INTO SomeValues VALUES ('D',4)
INSERT INTO SomeValues VALUES ('E',5)

SELECT S1.valuex AS median
FROM SomeValues AS S1, SomeValues AS S2
GROUP BY S1.valuex
HAVING SUM(CASE WHEN S2.valuex <= S1.valuex
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN S2.valuex >= S1.valuex
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1)

SQL For Smarties (Celko) has a whole chapter on how to calculate Medians in
SQL. Other statistics are also covered.
http://tinyurl.com/hntc


--
David Portas
------------
Please reply only to the newsgroup
--

"William Jiang" <williamj@qd.lucent.com> wrote in message
news:bm0gon$i2h@netnews.proxy.lucent.com...
> Recently, I will write a database application to get the median value
> according to the grouped condition.
>
> maybe, just like the following.
>
> SELECT Max(a1) MaxValue, Median(a1) MedianValue FROM test_table
>
> Any suggestion?
>
>