Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
1.33/5 (2 votes)
Can Any one Help me with query?
Posted
Comments
CHill60 17-Feb-14 8:20am    
You will notice that your question has been downvoted (not by me). This is likely because this exact same question gets asked for every new course intake. By asking it again you are demonstrating that you have put no effort what so ever into attempting this for yourself. Remember - your course tutors also look at this site!

Try this it's awesome query
Select * from (select dense_rank() over (order by salary desc) as rank,* from table)aa where aa.rank=N

here N is count of salry like 3rd,4th,5th etc..
 
Share this answer
 
Comments
Maciej Los 3-Mar-14 13:52pm    
You hit a 10!
Please, see my answer.
Andrius Leonavicius 3-Mar-14 15:57pm    
Nice answer, +5.
USe

SELECT TOP 1 Salary FROM( SELECT TOP 3 Salary FROM EmployeeSalary ORDER BY Salary DESC ) Q ORDER BY Salary ASC
 
Share this answer
 
Comments
Maciej Los 3-Mar-14 13:51pm    
It isn't true. It does not returns top high salary. Please see my answer.
SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP 3 Salary
FROM Employee
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary
 
Share this answer
 
v2
Comments
Maciej Los 3-Mar-14 13:52pm    
It isn't true. See my comment to the soultion no. 1
I would suggest to use ranking functions[^].

Have a look at below example:
SQL
DECLARE @emp_sal TABLE (empid INT, salary DECIMAL(8,2))

INSERT INTO @emp_sal (empid, salary)
VALUES (1, 2000.85),(3, 1850.00),(5, 1670.55),
(11, 1100.25),(15, 1100.00),(17, 2000.85),
(22, 1003.33),(24, 1666.66),(26, 1010.10),
(28, 1245.67),(41, 1134.56),(47, 1992.85),
(101, 1222.22),(113, 1333.33),(177, 1555.55)

--returns top high salary?
SELECT TOP (1) *
FROM (
    SELECT TOP (3) *
    FROM @emp_sal
    ORDER BY salary DESC
    ) AS T
-NO! return only salary for empid=1

--below query returns top high salaries!
SELECT RANK() OVER (ORDER BY salary DESC) AS RowNo, *
FROM @emp_sal
--empid 1 and 17; both have 2000.85 ;)
 
Share this answer
 
Comments
Andrius Leonavicius 3-Mar-14 15:53pm    
+5 for a clear explanation.
Maciej Los 3-Mar-14 16:04pm    
Thank you ;)
northwind database:


use this:

2nd Highest

SQL
select max(unitprice) from Products where UnitPrice not in (select top 1 UnitPrice from Products order by UnitPrice desc)


3rd highest

SQL
select max(unitprice) from Products where UnitPrice not in (select top 2 UnitPrice from Products order by UnitPrice desc)


4th highest

SQL
select max(unitprice) from Products where UnitPrice not in (select top 3 UnitPrice from Products order by UnitPrice desc)
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900