Nested group by with conditional query-CodePudding

I have 3 tables:

table: a

id  name
2   A
3   B

table: b

id  a        b                
3   2        Asd Ter Gsdt      
4   2        Gsd Gsdt Gsda
5   2        Asd Gsd
6   3        Uty Kggs
7   3        Tyud Hffddf

table: c

id  a           b
6   3           d       
7   3           a      
8   3           g
9   3           h
10  4           j
11  5           y
12  5           s
13  6           d
14  6           h

expected output:

a     b                 c           d
A     2019-04-06        3           a
B     2019-04-06        6           b

I am unsure how to proceed from this, how?

CodePudding user response：

This query do the job, but there is always a question about speed and performance.

select a.name,
(select c_date from c 
  join b on (c.b_id = b.id) 
  where b.a = a.id order by c_date desc limit 1) last_c_date,
  popular.b_id,
  (select photos->0 from b where id = popular.b_id) photo
from a
   join (
       select distinct on (a)
          b.id b_id, a from b 
             join c on (b.id = c.b_id)
          group by b.id, a
          order by a, count(*) desc, b.id
         ) popular on (popular.a = a.id) 
order by a.name

If there will be 2 equaly popular b objects in a region, query takes this with smaller id.

If it will be no b object with entries in c than subquery for photo can be surrounded with coalesce (but now it should work too with null value).