Using the SQL Over-Clause

Good Evening Experts,

This is an SQL Query that demonstates using the OVER Clause, used to specify all window functions within SQL Server 2005.  
And I am grappling with understanding the 3rd (OVER (PARTITION BY dept_id) max_sal_dept, salary) and 4th lines.  I can follow this statement up to (OVER (PARTITION BY dept_id), but what comes next I don't understand.  Can someone please explain this to me?

Thanks.
Studious01

SELECT dept_id, last_name, salary
FROM (SELECT dept_id, last_name, MAX(salary)
OVER (PARTITION BY dept_id) max_sal_dept, salary
FROM employee) AS part_deptid
WHERE salary = max_sal_dept
studious01Asked:
Who is Participating?
 
Einstine98Connect With a Mentor Commented:
Yes... because SQL server needs to reference this as an implicit view... otherwise how will you be able to build your Prinicpal WHERE clause?

so, if you write something like this

SELECT *
FROM (SELECT * FROM myTable)
JOIN (SELECT * FROM mySecondTable)
ON A = B
WHERE C = something

how will SQL server identify that A comes from the outcome of which query if they do not have aliases?
0
 
Einstine98Commented:

 MAX(salary) OVER (PARTITION BY dept_id) max_sal_dept,  --- will give you maximum salary per department
0
 
studious01Author Commented:
Yes, I kind of figured this out by dissecting this statement, and removing the subquery within MS SQL Server Mgmt Studio.
And I discovered that the "max_sal_dept" is just renaming the MAX(salary) column and it is just missing the "AS" that would normally precede a column name change. So I guess it isn't really needed.  But, I still don't understand what happens to "AS part_deptid".

Thanks.

Studious01
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Einstine98Commented:
AS Part_deptid is now the "table" that the main query is selecting from...

so, assume that you created a view like this:

Create View part_deptid AS
SELECT dept_id, last_name, MAX(salary)
OVER (PARTITION BY dept_id) max_sal_dept, salary
FROM employee

Then you did this

SELECT dept_id, last_name, salary
FROM part_deptid
WHERE salary = max_sal_dept

this is 100% the same as doing it all in one go

SELECT dept_id, last_name, salary
FROM (SELECT dept_id, last_name, MAX(salary)
OVER (PARTITION BY dept_id) max_sal_dept, salary
FROM employee) AS part_deptid
WHERE salary = max_sal_dept
0
 
studious01Author Commented:
OK, Einstine98 I understand now.  But, is it even be necessary to use "AS part_deptid" in the subquery?
0
 
studious01Author Commented:
Einstine98,

Thank you for helping me to obtain a better understanding of this.

Studious01
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.