From: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
---|---|
To: | Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: remove some rows from resultset |
Date: | 2012-04-19 06:59:26 |
Message-ID: | CAHnozTgnHERL26hvsTDurxv5zoWABGB9Y7rLgCKd3OjSx+2qaQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
should work. you could move b1 out of the sub query and add a normal where
clause to make the syntax nicer. Might also matter for the query plan.
other than that i don't see any bumps. It's good that you placed
"b2.org_specific_rule = true" in the join clause so that the left join
works properly.
why the mixed feelings?
WBL
2012/4/19 Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com>
> Hi,
>
> I have the following table:
> org_id | contract_name | org_specific_rule | count
> ----------+------------------+-------------------+-------
> smpj28p2 | Group 123 | f | 3
> smpj28p2 | Group 2 | f | 3
> smpj28p2 | Group 2 | t | 9
> smpj28p2 | Group 1 | f | 1
> w37mtn4r | Group 123 | f | 26
> w37mtn4r | Group 2 | f | 56
> w37mtn4r | Group 1 | f | 55
>
> Based on org_specific_rule and (org_id, contract_name) I need to transform
> this:
> smpj28p2 | Group 2 | f | 3
> smpj28p2 | Group 2 | t | 9
>
> to
> smpj28p2 | Group 2 | 9
>
> in other words:
> - if org_specific_rule = t then update "count" value in row where
> org_specific_rule = f to value from this row (3 was updated to 9)
> - remove org_specific_rule column
>
> I have query which does everything but I have mixed feelings about it:
> select
> b1.org_id, b1.contract_name, coalesce(b2.count, b1.count) as count
> from (select * from billing where org_specific_rule = false) as b1
> left join billing b2 on
> b1.org_id = b2.org_id
> and b1.contract_name = b2.contract_name
> and b2.org_specific_rule = true
> order by 1,2;
>
> org_id | contract_name | count
> ----------+------------------+----------
> smpj28p2 | Group 123 | 3
> smpj28p2 | Group 2 | 9
> smpj28p2 | Group 1 | 1
> w37mtn4r | Group 123 | 26
> w37mtn4r | Group 2 | 56
> w37mtn4r | Group 1 | 55
>
> Any ideas?
>
> --
> Ondrej Ivanic
> (ondrej(dot)ivanic(at)gmail(dot)com)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2012-04-19 07:00:07 | Re: remove some rows from resultset |
Previous Message | Willy-Bas Loos | 2012-04-19 06:48:37 | Re: Performance degrades until dump/restore |