Solved

Using the SQL Over-Clause

Posted on 2006-07-13
6
1,686 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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