Search code examples
sqlsqlplus

Order by the Compute of Count in SQL

Hi I get the results that I'm looking for however, I want to order it by the count from the compute. Anyone have any ideas.

Break on 'Movie Title' skip 2 
COMPUTE COUNT Label '# of Times Rented' of "Customer" on "Movie Title"
select film_title "Movie Title", star_rating "Customer Rating", 
        name "Customer"
from TITLE t join RENTAL r on(t.TITLE_NO = r.TITLE_NO)
    join CUSTOMER c on(r.CUSTOMER_NO = c.CUSTOMER_NO)
order by 1,3;

enter image description here

Solution

  • One approach is to use window functions to calculate and order by that value.

    The following orders the result by n descending, but doesn't include n in the final SELECT list.

    I kept your original order criteria to break any ties.

    Break on 'Movie Title' skip 2
COMPUTE COUNT Label '# of Times Rented' of "Customer" on "Movie Title"

WITH cte1 AS (
        SELECT film_title, star_rating
             , name
             , COUNT(*) OVER (PARTITION BY t.title_no) AS n
          FROM ddr_title t
          JOIN rental    r
            ON t.title_no = r.title_no
          JOIN customer c
            ON r.customer_no = c.customer_no
     )
SELECT film_title "Movie Title", star_rating "Customer Rating"
     , name "Customer"
  FROM cte1
 ORDER BY n DESC, 1, 3
;

    Example result:

    Movie Title                    Customer Rating Customer
------------------------------ --------------- ------------------------------
title1                                       3 Customer1
                                             3 Customer1
                                             3 Customer1
                                             3 Customer2
******************************                 ------------------------------
# of Times Rented                                                           4


title3                                       1 Customer1
                                             1 Customer2
******************************                 ------------------------------

Movie Title                    Customer Rating Customer
------------------------------ --------------- ------------------------------
# of Times Rented                                                           2


title2                                       2 Customer2
******************************                 ------------------------------
# of Times Rented                                                           1

7 rows selected.