On Wed, 23 Jun 2004 15:38:18 -0500, Jeffrey Melloy
>>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)
>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
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
SELECT co1, n1, l1, m1, sum(ore) AS sumore
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
(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).
In response to
- Re: sql at 2004-06-23 20:38:18 from Jeffrey Melloy
- sql at 2004-06-25 11:40:16 from Costin Manda
pgsql-novice by date
|Next:||From: Michael Guerin||Date: 2004-06-24 19:33:33|
|Subject: Re: database corruption|
|Previous:||From: derrick||Date: 2004-06-24 13:58:20|
|Subject: Re: pgHoster.com woes and looking for a new host|