CODEWITHSUNDEEP
sqlpostgresqlsql-order-bywindow-functions
Eliza Romanski
Eliza Romanski

Reputation: 93

what is ORDER BY useful for when i do PARTITION BY

i started to learn the PARTITION BY methode with agregate functions but i dont understand why should i use ORDER BY for in this query . i want to SUM the total amount of sales in $ on "standart paper " in each year this is my code :

    SELECT standard_amt_usd,
   DATE_TRUNC('year', occurred_at) as year,
   SUM(standard_amt_usd) OVER (PARTITION BY DATE_TRUNC('year', occurred_at) 
   ORDER BY occurred_at) AS running_total
   FROM orders ;

the answers i get are correct but i still dont understand why in need to ese ORDER BY in here and what will happen if i wont use it ? thank for the help:)

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270723

Run this query:

SELECT standard_amt_usd,
       DATE_TRUNC('year', occurred_at) as year,
       SUM(standard_amt_usd) OVER (PARTITION BY DATE_TRUNC('year', occurred_at) 
                                   ORDER BY occurred_at
                                  ) AS running_total,
       SUM(standard_amt_usd) OVER (PARTITION BY DATE_TRUNC('year', occurred_at) 
                                  ) AS group_total
FROM orders ;

You will probably see the difference right away. The ORDER BY says to do the summation "up to" this row. Without the ORDER BY, the summation is the same on all rows with the same PARTITION BY key.

Upvotes: 2

Hamza Haider
Hamza Haider

Reputation: 738

ORDER BY has mainly two rules:

  1. To actually define how another feature works. This is true when using TOP, say, or within an OVER() partition function. It doesn't require sorting to occur, it just says "this definition only makes sense if we consider the rows in the result set to occur in a particular order - here's the one I want to use"
  2. To dictate the sort order of the result set. This is true when it's an ORDER BY clause on the outermost statement that is part of a particular query - not in a subquery, a CTE, an OVER() paritition function, etc.

Upvotes: 0

Related Questions