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: neutral (nike.apache.org: local policy)
Message-ID: <5d1501c8aaa4$c8347a60$6e00a8c0@desktop2>
From: "Stephen Ince" <since@opendemand.com>
To: "Derby Discussion" <derby-user@db.apache.org>
References: <48181493.3080503@atreides.ro>
Subject: median query
Date: Wed, 30 Apr 2008 05:30:08 -0400
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="iso-8859-1";
	reply-type=response
Content-Transfer-Encoding: 7bit

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