Solved

# SQL Query

Posted on 2011-05-10
208 Views
Last Modified: 2012-05-11
Hello,
I have a question regarding how a query works:
select e1.Emp_Name,e1.Emp_Salary
from dbo.Emp e1
where 2 = (select COUNT(distinct (e2.Emp_Salary)) from dbo.Emp e2 where e2.Emp_Salary >= e1.Emp_Salary)

The above query returns second highest salary. How?
0
Question by:kreddy01
10 Comments

LVL 18

Expert Comment

What do you want to achieve?
0

Author Comment

i was reading through a blog and found the above query to select the second highest salary in an employee table. if i substitute 2 in the query with 3 i will get the third highest. How is it working?
0

LVL 18

Expert Comment

In SQL SERVER ? what version?
0

Author Comment

2008
0

LVL 18

Expert Comment

SELECT e1.Emp_Name,e1.Emp_Salary
ROW_NUMBER() OVER (ORDER BY ,e1.Emp_Salary) AS 'RowNumber'
from dbo.Emp e1
where 'RowNumber'  =2

0

LVL 15

Expert Comment

It's looping through all of the Emp table.  The sub query returns the number of salaries that are equal to or greater than the current employees salary.  Whenever it gets to the salary that only returns 2 results then it outputs that salary.
0

Author Comment

i know the query. I want to know how the below query is working

select e1.Emp_Name,e1.Emp_Salary
from dbo.Emp e1
where 2 = (select COUNT(distinct (e2.Emp_Salary)) from dbo.Emp e2 where e2.Emp_Salary >= e1.Emp_Salary)
0

LVL 15

Accepted Solution

This part loops through the entire table row by row

select e1.Emp_Name,e1.Emp_Salary
from dbo.Emp e1

The salary of whichever row it's on gets passed to this subquery

(select COUNT(distinct (e2.Emp_Salary)) from dbo.Emp e2 where e2.Emp_Salary >= e1.Emp_Salary)

Whenever that equals 2 (Or 3 or 4) the first query returns the row it was currently on.
0

LVL 59

Assisted Solution

I agree with tim_cs.  If you are looking to rewrite this using windowing functions as shown earlier, it is more of a DENSE_RANK() than ROW_NUMBER().  The COUNT(DISTINCT ...) is ensuring that if you have 10 employees for example all with the highest salary that they all would come out as rank of 1 (or having 1 salary >= to their own).

SELECT Emp_Name, Emp_Salary
FROM (
SELECT Emp_Name, Emp_Salary
, DENSE_RANK() OVER(ORDER BY Emp_Salary DESC) RN
FROM dbo.Emp
) derived
WHERE RN = 2 -- whichever salary you want to see

It is a little more complex, but if you run the inner query by itself, it should give you an idea of how it is ranking the employees and their salaries and help illustrate what essentially is happening with the subquery from your original query above.
0

Author Comment

tim_cs and mcvisa1...you guys are kooool....thank You....i now understand how its working.
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### 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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!