Обсуждение: remove some rows from resultset

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.ivanic@gmail.com)

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




--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw

On 19 Apr 2012, at 6:26, Ondrej Ivanič wrote:

> 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;


You don't need the subquery. Also, if you can have multiple 'false' rows for the same unique identifier, you'll want to
sumthem. 

Untested, but I think this is what you want:

select
   b1.org_id, b1.contract_name, sum(b1.count) + sum(coalesce(b2.count, 0))  as count
from billing 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 = false
   and b1.org_specific_rule = true
group by b1.org_id, b1.contract_name
order by b1.org_id, b1.contract_name;


Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.