The PARTITION BY clause sets the range of records that will be used for each “GROUP” within the OVER clause. In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you’re de-nomalising the emp table; you still return every record in the emp table.) If there was another column (e.g., state) then you could … Read more
RANK gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5. DENSE_RANK again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are … Read more
They’re used in different places. group by modifies the entire query, like: But partition by just works on a window function, like row_number: A group by normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row. partition by does not affect the number of rows returned, … Read more