Re: sql

-----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(at)ca(dot)afilias(dot)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-----