What are some examples of SQL window functions?

What are some examples of SQL window functions?

SQL window functions perform calculations across a set of table rows related to the current row. Examples include ROW_NUMBER(), RANK(), and SUM(). Would you like to see specific examples?

Yes, please provide an example using ROW_NUMBER().

Sure! Here's an example:

SELECT employee_id, department_id, 
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank 
FROM employees;

This query assigns a unique rank to each employee within their department based on their salary.

That's helpful! Can you explain how RANK() differs from ROW_NUMBER()?

Absolutely! The RANK() function assigns the same rank to rows with equal values, leaving gaps in the ranking. For example, if two employees have the same highest salary, they both get a rank of 1, and the next rank will be 3. In contrast, ROW_NUMBER() assigns a unique number to each row regardless of ties.

Got it! Can you give me an example of using RANK()?

Certainly! Here's an example:

SELECT employee_id, salary, 
       RANK() OVER (ORDER BY salary DESC) as salary_rank 
FROM employees;

This will rank employees based on their salary, with ties receiving the same rank.