Обсуждение: sql
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!
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.
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
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
-----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-----