[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Using the SQL Over-Clause

Posted on 2006-07-13
6
Medium Priority
?
1,713 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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