Thread: sql

sql

From

"cristi"

Date:

23 June 2004, 10:27:26

I have the following table structure:

CREATE TABLE "xxx" (
 "co" character varying(7),
 "co1" character varying(9),
 "n1" character varying(15),
 "l1" character varying(5),
 "m1" smallint,
 "ore" bigint
);

who contains the following date:

22021CC 1044 637 S5G8 407 5
22021CC 1044 637 S5G8 409 47
22021CD 1044 637 S5G8 410 24
22022BB 1044 637 S5G8 409 10

I need a SQL select which result to be:

22021CC 1044 637 S5G8 407 5
22021CC 1044 637 S5G8 409 57
22021CD 1044 637 S5G8 410 24

I mean:
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.

Wou!
Thanks in advance!

Re: sql

From

Jeffrey Melloy

Date:

23 June 2004, 20:38:30

cristi wrote:

>I have the following table structure:
>
>CREATE TABLE "xxx" (
> "co" character varying(7),
> "co1" character varying(9),
> "n1" character varying(15),
> "l1" character varying(5),
> "m1" smallint,
> "ore" bigint
>);
>
>who contains the following date:
>
>22021CC 1044 637 S5G8 407 5
>22021CC 1044 637 S5G8 409 47
>22021CD 1044 637 S5G8 410 24
>22022BB 1044 637 S5G8 409 10
>
>I need a SQL select which result to be:
>
>22021CC 1044 637 S5G8 407 5
>22021CC 1044 637 S5G8 409 57
>22021CD 1044 637 S5G8 410 24
>
>I mean:
>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.
>
>Wou!
>Thanks in advance!
>
>

What you want is an aggregate.  Saying "adding to that value the value
of the records which are not selected" is kind of a weird way to say it,
though.

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.

Re: sql

From

Manfred Koizar

Date:

24 June 2004, 18:21:29

On Wed, 23 Jun 2004 15:38:18 -0500, Jeffrey Melloy
<jmelloy@visualdistortion.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

sql

From

"Costin Manda"

Date:

25 June 2004, 11:36:59

Actually, it can be done with a temporary table, as I have already told
Cristi (short for Cristian, btw :) ) like this:

SELECT m1, max (ore) AS mx, sum(ore) AS sm INTO TEMPORARY tmp FROM xxx GROUP
BY m1;
SELECT co,co1,n1,l1,tmp.m1,sm FROM xxx JOIN tmp ON xxx.m1=tmp.m1 AND
xxx.ore=tmp.mx ORDER BY tmp.m1 asc;

Ain't I a stinker? :)

I have to ask you guys if you know if this way is faster than making a
script that computes the max and the sum while parsing the table. Because
that join could slow things down, I guess...

The reason for this post (besides that of bragging) is that people bump into
similar problems all the time.
Like finding the values in the row that has one value maximum. You can't do
"select a,b,c,d from table where d=max(d) group by c";
The above example is my solution to it. I invite you to find other
solutions, maybe more ellegant.

Siderite

Re: sql

From

Andrew Hammond

Date:

25 June 2004, 15:32:33

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

cristi wrote:
| I have the following table structure:
|
| CREATE TABLE "xxx" (
|  "co" character varying(7),
|  "co1" character varying(9),
|  "n1" character varying(15),
|  "l1" character varying(5),
|  "m1" smallint,
|  "ore" bigint
| );
|
| who contains the following date:
|
| 22021CC 1044 637 S5G8 407 5
| 22021CC 1044 637 S5G8 409 47
| 22021CD 1044 637 S5G8 410 24
| 22022BB 1044 637 S5G8 409 10
|
| I need a SQL select which result to be:
|
| 22021CC 1044 637 S5G8 407 5
| 22021CC 1044 637 S5G8 409 57
| 22021CD 1044 637 S5G8 410 24
|
| I mean:
| 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.

If I understand what you're asking correctly,

SELECT co, col, nl, l1, m1, max(ore) as ore, sum(ore) sum_of_ore
FROM xxx
GROUP BY co, col, n1, l1, m1;

Normally, you want to avoid using the max() aggregate function since it
forces a table scan. In this case, you're using the sum() aggregate and
can't avoid paying for a table scan.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFA3EbPgfzn5SevSpoRAhj8AKCs7lkGW4J8Fz+y/9jEoI6uOExw1wCcC0//
payEABj1tkWPLT3HENnxrZo=
=Net6
-----END PGP SIGNATURE-----

Attachment

ahammond.vcf