Unfortunately for DB2 for i we can't use the OVER() clause on COUNT, SUM,
and other aggregate functions, although you shouldn't need that for this.
The following should work on DB2 for i, although I can't test it now;
CREATE TABLE T(c1 INT);
INSERT INTO T VALUES 10, 12, 18, 30, 33, 50;
with cte as (SELECT c1, ROW_NUMBER() OVER() as rn, count(c1) AS cnt FROM T
ORDER BY c1),
cte2 as (SELECT A.c1, B.c1 as c1next, A.rn, A.cnt from cte A JOIN cte B on
A.rn + 1 = B.rn)
SELECT CASE when MOD(cnt, 2) = 0 then (c1 + c1next)/2 else c1 end as
medianValue FROM cte2
WHERE rn = (cnt + 1) / 2;
I'm probably missing some edge cases, but my cte2 is basically doing
the LEAD function that is present in DB2 LUW and other RDBMS. Then I
am doing a test for an even or odd count of records to pick the
average of the two middle numbers or the middle number itself.
Hope this helps you get started.
On Mon, Jan 6, 2014 at 2:07 PM, Matt Olson <Matt.Olson@xxxxxxxx> wrote:
Doesn't seem to work on IBM I's DB2 variant.
Get syntax error at (
Presumably because of count(1) over()
Not sure what to use for alternative syntax however.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives