Solved

Using the SQL Over-Clause

Posted on 2006-07-13
6
1,678 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 250 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now