Magic of Window Function

Consider the magic of window functions. Let’s say you have an employee table with nam, department and salary. Now user want to find the top 2 employees in each department by their salary level in the department.

Without window function, you will feel some headache. With window function, it will be simple and grace. Just one statement.

SELECT name, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS salary_rank_in_department
FROM HR.Employees

Of course you will need some minor changes to get the result user was asking, e.g. encapsulate the statment above in a CTE.

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: