db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: median query
Date Wed, 30 Apr 2008 14:13:29 GMT
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


Mime
View raw message