RE: Calc median and quartiles in SQL on IBM i

I'm trying to keep my code compatible back to v5r3, but may be forced to go
up to v5r4 (I have a v5r4 system and a v7r1 system).

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, February 28, 2012 11:14 AM
To: Midrange Systems Technical Discussion
Subject: Re: Calc median and quartiles in SQL on IBM i

You should be able to adapt the code found online...

Probably would be easiest if you're running on 7.1, but even 5.4 has
PARTITION/RANK OVER()...

Charles


On Tue, Feb 28, 2012 at 11:59 AM, Thomas Garvey <tgarvey@xxxxxxxxxx> wrote:

Hi,

Anybody got an SQL statement that will (reliably) select the median
value from a table of numeric values?  By reliably I mean no matter
what the count of numeric values is (even or odd).  It's got to work on

the IBM i.

 Even better, how about calculating the 1st (25% of values are less
than
this) and 3rd quartiles (75% of values are less than this) from the table?

These have to work on the IBM i.  I've seen several techniques online
but none that use only the features of IBM SQL (no OVER phrase, etc.)

Thanks for any advice.

Tom Garvey
--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.