Skip site navigation (1) Skip section navigation (2)

Re: sql

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org>
Cc: cristi <cristi(at)dmhi(dot)ct(dot)ro>, pgsql-novice(at)postgresql(dot)org
Subject: Re: sql
Date: 2004-06-24 18:20:43
Message-ID: dt5md0dtp0u9ecuh425a1c17gcdkjs66hn@email.aon.at (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

  • Re: sql at 2004-06-23 20:38:18 from Jeffrey Melloy

Responses

  • sql at 2004-06-25 11:40:16 from Costin Manda

pgsql-novice by date

Next:From: Michael GuerinDate: 2004-06-24 19:33:33
Subject: Re: database corruption
Previous:From: derrickDate: 2004-06-24 13:58:20
Subject: Re: pgHoster.com woes and looking for a new host

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group