Re: sql

On Wed, 23 Jun 2004 15:38:18 -0500, Jeffrey Melloy
<jmelloy(at)visualdistortion(dot)org> wrote:
>>I want to select the records wich for co1,n1,l1,m1 value are the same and
>>has the maxim value of the ore field
>>adding to that value the value of the records which are not selected.

>select co, co1, n1, l1, m1, sum(ore)
>from xxx
>where [...]
>group by co, co1, n1, l1, m1
>
>Should get you exactly what you need.

Almost. Cristi said that she did not want to group by co. The query
should return the co value belonging to the max ore value per group.
This can be done with the non-standard Postgres extension DISTINCT ON:

SELECT DISTINCT ON (co1, n1, l1, m1) co, co1, n1, l1, m1, ore
FROM xxx
ORDER BY co1, n1, l1, m1, ore DESC;

I don't believe that the DISTINCT ON thing can be done together with
sum(ore) in one simple query, so sum(ore) has to be calculated in its
own query

SELECT co1, n1, l1, m1, sum(ore) AS sumore
FROM xxx
GROUP BY co1, n1, l1, m1;

... and the two results have to be joined:

SELECT a.co, a.co1, a.n1, a.l1, a.m1, b.sumore
FROM (SELECT DISTINCT ON ....) AS a
INNER JOIN
(SELECT ... GROUP BY ...) AS b
ON a.co1=b.co1 AND a.n1=b.n1 AND a.l1=b.l1 AND a.m1=b.m1;

HTH (and hope I got it right).

Servus
Manfred