Re: sql

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.