db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Stephen Ince" <si...@opendemand.com>
Subject median query
Date Wed, 30 Apr 2008 09:30:08 GMT
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