Here is the schema for database
Here is the code that i wrote:
select q.cName,q.ctgName , max(q.count) as max_count
from (
select ct.city as cName ,ctg.name as ctgName, count(ctg.name) as count
from city as ct
join address as addr on ct.city_id = addr.city_id
join customer as cs on cs.address_id = addr.address_id
join rental as r on r.customer_id = cs.customer_id
join inventory as i on i.inventory_id = r.inventory_id
join film as f on f.film_id = i.film_id
join film_category as fc on fc.film_id = f.film_id
join category as ctg on ctg.category_id = fc.category_id
group by (ct.city,ctg.name)
order by count desc
) q
group by (q.cName,q.ctgName)
order by(q.cName) desc
When i remove q.ctgName parts it works fine but in that case , i am not able to fetch category names. How can i solve this. Im using PostgreSQL11
CodePudding user response:
Your inner query looks fine, although with this over-normalized model, the Film table is not strictly needed in this query.
So to really simplify this I would add one column to your inner query, the city rank of each category. Then your outer query can simply select all records where city rank = 1.
, rank() over (Partition By Ct.city Order by count(*) desc) as cityrank
Thus,
Select * from (
select ct.city as cName ,ctg.name as ctgName, count(ctg.name) as count
, rank() over (Partition By Ct.city Order by count(*) desc) as cityrank
from city as ct
join address as addr on ct.city_id = addr.city_id
join customer as cs on cs.address_id = addr.address_id
join rental as r on r.customer_id = cs.customer_id
join inventory as i on i.inventory_id = r.inventory_id
join film as f on f.film_id = i.film_id
join film_category as fc on fc.film_id = f.film_id
join category as ctg on ctg.category_id = fc.category_id
group by (ct.city,ctg.name)
)
Where cityrank=1
Order by cName
Note that rank() can produce two (or more) number one categories when the counts are the same.