SQL windows function in PostgreSQL

Question

I am new in SQL and I try to query a database using PostgreSQL (9.6).

When I write the following code I have and error syntax with the windows function:

/* Ranking total of rental movie by film category (I use the sakila database) */

SELECT category_name, rental_count
FROM
    (SELECT c.name category_name, Count(r.rental_id) rental_count
    FROM category c
    JOIN film_category USING (category_id)
    JOIN inventory USING (film_id)
    JOIN rental r USING (inventory_id)
    JOIN film f USING (film_id)
    GROUP BY 1, 2
    ORDER by 2, 1
     ) sub
RANK() OVER (PARTITION BY category_name ORDER BY rental_count DESC) AS total_rank

Gordon Linoff · Accepted Answer · 2018-09-17 15:19:10Z

1

You don't need a subquery:

SELECT c.name as category_name, COUNT(*) as rental_count,
       ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY COUNT(*) DESC)
FROM category c JOIN
     film_category USING (category_id) JOIN
     inventory USING (film_id) JOIN
     rental r USING (inventory_id) JOIN
     film f USING (film_id)
GROUP BY 1
ORDER by 2, 1;

You also don't need the join to film, because you are using nothing from that table.

Your query fails because the column list goes in the SELECT clause. The FROM list follows the SELECT.

edited Sep 17, 2018 at 15:19

answered Sep 17, 2018 at 13:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

fyardlest Over a year ago

I have the same output when using this: SELECT category_name, rental_count, RANK() OVER (PARTITION BY category_name ORDER BY rental_count DESC) AS total_rank FROM (SELECT c.name category_name, Count(r.rental_id) rental_count FROM category c JOIN film_category USING (category_id) JOIN inventory USING (film_id) JOIN rental r USING (inventory_id) JOIN film f USING (film_id) GROUP BY 1 ) sub ORDER by 2, 1;

fyardlest Over a year ago

Thank you so much dear Gordon Linoff, you are right I really don;t need a subquery, and I see clear now with your answer. You are the best my friend. Thank you again! By the way I just use the GROUP BY 1 instead of GROUP BY 1, 2.

Collectives™ on Stack Overflow

SQL windows function in PostgreSQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related