Get specific rows from group by clause in PostgreSQL

Question

If I've a table like below,

tid   pid   bid  fl fq fo
---------------------------
7114  3823  2341  3  1  1
7114  3823  2340  0  0  0
7114  3823  2350  0  0  0
7114  3850  4515  0  0  0
7114  3474  2350  0  0  0
7114  3474  2340  1  2  1

from this table I need to get the rows of columns pid,bid,fl and fq where its fo=1 by grouping pid

and the expected output will be:

pid   bid   fl fq fo
----------------------
3823  2341   3   1   1
3823  2340   3   1   1
3823  2350   3   1   1

3474  2350   1   2   1
3474  2340   1   2   1

NOTE :for example (consider the table) 1st and 2nd row have the same pid ie.3823 and among those two rows one row has fo=1(ie. 1st row) so i need to get the pid of 1st row and the fl,fq and bid of the second row,so the output should be

pid   bid   fl fq fo
----------------------
3823  2341   3   1   1
3823  2340   3   1   1
3823  2350   3   1   1

sample data:

create table com (tid int,pid int,bid int,fl int,fq int,fo int);

insert into com  values (7114 , 3823,  2341,  3 , 1 , 1),
(7114 , 3823 , 2340 , 0  ,0 , 0),(7114 , 3823 , 2350 , 0 , 0 , 0),
(7114  ,3850,  4515,  0 , 0 , 0),(7114 , 3474 , 2350,  0 , 0,  0),
(7114  ,3474,  2340 , 1 , 2 , 1);

@ClodoaldoNeto almas answer is incorrect,Need to group pid — octobot
– octobot, Commented Dec 30, 2014 at 11:49
You need to decide what to do with the rest of the columns you select, do you want max values, or summed values etc? — jarlh
– jarlh, Commented Dec 30, 2014 at 11:50
@jarlh and @ClodoaldoNeto see for example 1st and 2nd row have the same pid ie.3823 and among those two rows one row has fo=1 so i need to get the pid of 1st row and the fl,fq and bid of the second row — octobot
– octobot, Commented Dec 30, 2014 at 11:53

HoneyBadger · Accepted Answer · 2014-12-30 12:14:06Z

2

Try this:

select      distinct c1.pid
,           c2.fl
,           c2.fq
,           c2.bid
from        tmp.com c1
inner join  tmp.com c2
        on  c1.pid = c2.pid
        and c2.fo = 1

output:
    3474    1   2   2340
    3823    3   1   2341

A distinct is neccesary to prevent duplication, you can also join on more fields if you can.

answered Dec 30, 2014 at 12:14

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HoneyBadger Over a year ago

From your example it appears you need the bid of the row where fo <> 0. Then what about the 3rd row where pid is also 3823 and fo <> 0?

octobot Over a year ago

please see my updated expected output , and yes your solution almost reached my output but need to avoid distinct ie. select c1.pid,c1.bid,c1.fl,c1.fq,c1.fo from com c1 inner join com c2 using (pid) where c2.fo=1

Vivek S. · Accepted Answer · 2014-12-31 05:48:18Z

1

I guess you're using PostgreSQL Database if so then try this :

1.) Create A Temporary Table

create temp table temp_com as select pid,bid,fl,fq,fo from  com limit 0;

2.) Use CTE to get the values according to you're criteria and

Insert - pid,bid to temp_com using CTE

with cte as (
            select c1.pid,c1.bid,c1.fl,c1.fq,c1.fo 
            from com c1 inner join com c2 using (pid) 
            where c2.fo=1
)
insert into temp_com (pid,bid) (select pid,bid from cte);

Update - fl,fq and fo from CTE

with cte as(
           select c1.pid,c1.bid,c1.fl,c1.fq,c1.fo 
           from com c1 inner join com c2 using (pid) 
           where c2.fo=1 
)
update temp_com a 
set fl= cte.fl,
    fq=cte.fq,
    fo=cte.fo 
from cte 
where a.pid=cte.pid and cte.fo=1; -- gets the row have `fo=1`

And the Result : select * from temp_com;

pid   bid   fl fq  fo
----------------------
3823  2341  3   1   1
3823  2340  3   1   1
3823  2350  3   1   1
3474  2350  1   2   1
3474  2340  1   2   1

edited Dec 31, 2014 at 5:48

answered Dec 31, 2014 at 5:41

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

1 Comment

Vivek S. Over a year ago

@tamirda Am not sure that my solution doesn't have any caveats but with the given sample it worked well in my environment

Collectives™ on Stack Overflow

Get specific rows from group by clause in PostgreSQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related