how to find nth highest salary
Answers were Sorted based on User's Feedback
Answer / manshul
select * from employee e, (select salary, rownum rn from
(select distinct salary from employee order by salary desc))
T where T.sal= e.sal and T.rn = 4
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / vidit
DECLARE @SQL VARCHAR(2000), @N INT
SET @N = 5
SET @N = @N - 1
SET @sql = 'select top 1 salary from ABC where salary not
in ( SELECT TOP ' + CAST(@n AS VARCHAR(100)) + ' salary
FROM ABC order by salary desc ) ' + ' order by salary desc '
SELECT @SQL
EXEC @SQL
Is This Answer Correct ? | 4 Yes | 1 No |
Answer / anonymous
select min(salary) from(
select salary from test
order by salary desc
)
where rownum<4
For ORACLE 8i,9i,10g
select distinct salary from
(select salary,
dense_rank() OVER (PARTITION BY empname ORDER BY salary
desc) rank
from test) where rank=7
;
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / dewashish kumar pal
select salary from tablename t where (n-1)= (select count(*) from tablename where salary > t.salary);
note:- t = tablename
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / vishal patel
with cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY ecc_dm_id_dep ORDER
BY ecc_gross_simple DESC) AS RowID,
*
FROM emp_curr_company
)
SELECT *
FROM cte
WHERE RowID = 2
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / palash
question is which db are we looking at..
sqlserver or oracle
if sqlserver then top function is readily available to get
the nth highest sal etc
what if it the db is oracle, oracle does not have a
implicit top function to use so how do we go about it
couple of ways
1) use analytical queries
2) use co-related queries (suitable in small sized
databases)
1) analytical queries
if looking for nth highest within the complete table
select * from (select sal , dense_rank() over(order by sal
desc) rnk from emp ) where rnk = n
we can use row_number/rank functions also in place of
dense_rank.
if looking for nth highest within each department.
select * from (select sal, dense_rank() over (partition by
dept order by sal) rnk from emp) where rnk = n
2) co-related queries:
select sal from emp e1 where (n-1) = (select count(1) from
emp e2 where e2.sal > e1.sal)
this query will be pretty slow if the size of the table is
huge.
so my advice is to use the analytical version which is much
much faster than the co-related version.
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / dewashish kumar pal
Select max(salary) from tablename where salary not in (select max(salary) from tablename);
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / abhishek singh
SELECT TOP 1 salary
FROM dbo.emp
WHERE (salary IN
(SELECT DISTINCT TOP n salary
FROM dbo.emp
ORDER BY salary DESC))
ORDER BY salary
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / gopi mandadi
Select emp_no,name,dept,sal from emp_t A
Where &n = (Select sal from emp_t B Where A.sal <= b.sal)
Is This Answer Correct ? | 1 Yes | 0 No |
Write a query to include a constraint, to check whether the employee salary is greater than 5000?
How do I install only the client tools of sql server 2000?
How many databases instances are there in sql server 2000?
Why transaction is important?
what type of index will get created after executing the above statement? : Sql server database administration
How can you get @@error and @@rowcount at the same time?
How the authentication mode can be changed?
How to delete an existing database user?
What are the basic functions for master, msdb, model, tempdb and resource databases?
what's the maximum size of a row? : Sql server database administration
what is Full Text Search ?
Can a trigger be created on a view?