CODEWITHSUNDEEP
postgresqlsortingpostgresql-8.4
Skartt
Skartt

Reputation: 591

Sort partitions and rows inside the partitions

I am following this tutorial: http://www.postgresqltutorial.com/postgresql-window-function/

I'm looking for a case that is not described in the tutorial and I don't found a solution.

At one moment on the tutorial, this SELECT query is used to display the products grouped by group name and their prices sorted ascending in each group, here is the result :

Sorted by prices inside the partition

the request is :

SELECT
 product_name,
 group_name,
 price,
 ROW_NUMBER () OVER (
 PARTITION BY group_name
 ORDER BY
 price
 )
FROM
 products
INNER JOIN product_groups USING (group_id);

I would like to sort the rows by price like in the example AND to sort the partition by descending alphabetical order, like this :

Sorted by prices inside the partition and partitions sorted by name

How can modify the request to obtain this result ?

Upvotes: 0

Views: 501

Answers (1)

unutbu
unutbu

Reputation: 879919

ORDER BY can be followed by a comma-separated list of sort_expressions. Use ASC or DESC to set the sort direction for each expression. ASC (ascending order) is the default sort direction.

Thus, you could use ORDER BY group_name DESC, price: 

SELECT
 product_name,
 group_name,
 price,
 ROW_NUMBER () OVER (
 PARTITION BY group_name
 ORDER BY
 group_name DESC, price 
 )
FROM
 products
INNER JOIN product_groups USING (group_id);

yields

| product_name       | group_name |   price | row_number |
|--------------------+------------+---------+------------|
| Kindle Fire        | Tablet     |  150.00 |          1 |
| Samsung Galaxy Tab | Tablet     |  200.00 |          2 |
| iPad               | Tablet     |  700.00 |          3 |
| Microsoft Lumia    | Smartphone |  200.00 |          1 |
| HTC One            | Smartphone |  400.00 |          2 |
| Nexus              | Smartphone |  500.00 |          3 |
| iPhone             | Smartphone |  900.00 |          4 |
| Lenovo Thinkpad    | Laptop     |  700.00 |          1 |
| Sony VAIO          | Laptop     |  700.00 |          2 |
| Dell Vostro        | Laptop     |  800.00 |          3 |
| HP Elite           | Laptop     | 1200.00 |          4 |

Upvotes: 2

Related Questions