How to display all rows based on column frequency PostgreSQL

Question

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.

score 0 · Accepted Answer · answered Sep 30 '21 at 02:29

0

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.

answered Sep 30 '21 at 02:29

Erwin Brandstetter

605,456
145
1,078
1,228

How would this code change if we wanted the Product order to stay the same? – Smithy Sep 30 '21 at 02:41
@Ant: What product order? By some undeclared `id`? There is no natural order in a relational table. – Erwin Brandstetter Sep 30 '21 at 02:42
For instance, in this question products were ordered by alphabetical order. However, what if you wanted products to be returned in the order before reordering by the Customer count. For instance it would appear as this Jones Jones -> Shoes, Top, Bottom – Smithy Sep 30 '21 at 02:44
There was ***no*** order before. You have listed rows in a certain order, but there is no such order in a `SELECT` unless instructed by `ORDER BY`. Don't confuse a relational table with an spreadsheet. See: https://stackoverflow.com/a/22967310/939860 – Erwin Brandstetter Sep 30 '21 at 02:47

score 0 · Answer 2 · answered Sep 30 '21 at 02:32

0

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;

answered Sep 30 '21 at 02:32

Ajax1234

69,937
8
61
102

The correlated subquery in the `ORDER BY` would scale terribly with O(N³) due to the unrestricted self-join. – Erwin Brandstetter Sep 30 '21 at 02:41

How to display all rows based on column frequency PostgreSQL

2 Answers2