Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: local policy)
Date: Wed, 30 Apr 2008 07:13:29 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: median query
In-reply-to: <5d1501c8aaa4$c8347a60$6e00a8c0@desktop2>
Sender: Richard.Hillegas@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <48187E89.60501@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <48181493.3080503@atreides.ro>
 <5d1501c8aaa4$c8347a60$6e00a8c0@desktop2>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)

Hi Stephen,

You may be able to write a user function to help. There's an example of 
a median-calculating user function in the "scores" demo. For an example 
of how to write and use this function, search for "getMedianTestScore" 
in the demo subtree of your Derby distribution (it should be available 
from release 10.3.1.4 onward).

Hope this helps,
-Rick

Stephen Ince wrote:
> I was trying to write a query that would return the median.
> I tried the following approached.
> 1) sql window functions, row_number.
>   Can use this because derby can not on a column.
>
> 2) Cross-join technique.
>  This fails because derby does not have good case support. I am 
> getting null pointer exception.
>
> SELECT P1.weight
> FROM Parts AS P1, Parts AS P2
> GROUP BY P1.weight
> HAVING SUM(CASE WHEN P2.weight <= P1.weight
> THEN 1 ELSE 0 END)
>> = ((COUNT(*) + 1) / 2)
> AND SUM(CASE WHEN P2.weight >= P1.weight
> THEN 1 ELSE 0 END)
>> = (COUNT(*)/2 + 1);
>
>
> Has anyone been success in writing a sql median query.
>
> Steve