Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

SQL Query

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
kreddy01
Asked:
kreddy01
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
dj_alikCommented:
What do you want to achieve?
0
 
kreddy01Author Commented:
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
 
dj_alikCommented:
In SQL SERVER ? what version?
0
Industry Leaders: 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!

 
kreddy01Author Commented:
2008
0
 
dj_alikCommented:
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
 
tim_csCommented:
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
 
kreddy01Author Commented:
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
 
tim_csCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
kreddy01Author Commented:
tim_cs and mcvisa1...you guys are kooool....thank You....i now understand how its working.
0

Featured Post

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!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now