How to display all rows based on column frequency PostgreSQL-CodePudding

How do we output columns based on the count of customer and order products by alphabetical order?
Given the following table

Customer      | Product 
------------------------
James Jones   | Shoes
James Jones   | Top
Jay Smith     | Bottom
Jones Jones   | Shoes
Jones Jones   | Top
Jones Jones   | Bottom

Output

Customer      | Product 
------------------------
Jones Jones   | Bottom
Jones Jones   | Shoes
Jones Jones   | Top
James Jones   | Shoes
James Jones   | Top
Jay Smith     | Bottom

Where Jones Jones is shown first as he has the highest amount of products and products are displayed in alphabetical order. This is then repeated for other customers based on product count.

CodePudding user response：

One way is to add the count per customer with a window function in a subquery, and then order by that.

SELECT customer, product
FROM  (
   SELECT customer, product
        , count(*) OVER (PARTITION BY customer) AS ct
   FROM   tbl
   ) sub
ORDER  BY ct DESC, customer, product;

Since multiple customer may have the same number of products, add more ORDER BY expressions to get a deterministic sort order.

CodePudding user response：

You can use a subquery with sum:

select t1.* from t t1 
order by (select sum(case when t1.customer = t2.customer then 1 end) from t t2) desc, t1.product asc;