Re: Median with standard SQL

From: Larry Coon <larry_at_assist.org>
Date: Mon, 09 Jul 2001 11:36:23 -0700
Message-ID: <3B49F9A7.5AA7@assist.org>

Jess Likens wrote:

> Does anyone have a method to get the median of a relation using only the
> following SQL commands: SELECT, DISTINCT, WHERE, FROM, LIKE, EXISTS, IN,
> UNION, INTERSECT, EXCEPT/MINUS, ANY, ALL, COUNT, SUM, AVG, MAX, MIN, GROUP
> BY, HAVING, ORDER BY, JOIN? I can't seem to come up with anything. The
> median of n elements occurs at (n+1)/2 for an odd number of elements and it
> is the average of n/2 and n/2 and (n+1)/2. Any help would be much
> appreciated.

Celko's "SQL for Smarties" devotes over 10 pages to medians, and gives several solutions. You might want to take a look there.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Mon Jul 09 2001 - 13:36:23 CDT