?
Solved

Using the SQL Over-Clause

Posted on 2006-07-13
6
Medium Priority
?
1,709 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:studious01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:Einstine98
ID: 17105583

 MAX(salary) OVER (PARTITION BY dept_id) max_sal_dept,  --- will give you maximum salary per department
0
 

Author Comment

by:studious01
ID: 17105669
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17106008
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:studious01
ID: 17126982
OK, Einstine98 I understand now.  But, is it even be necessary to use "AS part_deptid" in the subquery?
0
 
LVL 12

Accepted Solution

by:
Einstine98 earned 1000 total points
ID: 17127409
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
 

Author Comment

by:studious01
ID: 17143250
Einstine98,

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

Studious01
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question