how to find nth highest salary
Answers were Sorted based on User's Feedback
Answer / nandkumar karlekar
Take this script and try u will get nth salary as you wish
create Table EmpTest
(
ID bigint,
Sal bigint
)
go
insert into emptest
values
(1,100)
insert into emptest
values
(2,200)
insert into emptest
values
(3,300)
insert into emptest
values
(4,400)
insert into emptest
values
(5,500)
insert into emptest
values
(6,600)
go
DECLARE @n bigint
--specify your nth salary
SET @n=3
select top 1 * from Emptest
where @n <= (select Count(*) from Emptest EE where EE.sal
>Emptest.sal)
order by sal desc
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / latha k
declare @N INT
SET @N = 3
SELECT *
FROM [user] AS [user]
WHERE @N = (SELECT COUNT(DISTINCT(user1.Salary))
FROM [user] AS [user1]
WHERE user.Salary <= user1.Salary
)
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / dilip
SELECT MIN(SALARY) FROM tablealies.Table_Name WHERE SALARY
IN (SELECT DISTINCT TOP N MAX(SALARY) FROM
tablealies.Table_Name ORDER BY SALARY DESC)
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / premsagar12
SELECT DISTINCT(A.SAL),ENAME FROM EMP A WHERE &N=(SELECT
COUNT(DISTINCT(B.SAL )) FROM EMP B WHERE A.SAL<=B.SAL);
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / dinesh
select distinct(a.salary)
from Salarydtls a
where 3= (select distinct(count(b.salary))
from salarydtls b
where a.salary<=b.salary)
ans is for third highest sal
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / raman sharma
For third highest salary u can use this syntax
select min(cin) from ( select distinct cin from
cws.usertable order by cin desc ) where rownum<4
| Is This Answer Correct ? | 0 Yes | 3 No |
Answer / bala
-- to find third highest salary from emp table
select empname,empsal from emp a where 3 = (select count(*)
from emp b where a.empsal <= b.empsal);
| Is This Answer Correct ? | 6 Yes | 12 No |
Answer / sysdomain
'top' will fetch only top most : ie top 5 means it will
fetch first 5 rec. After fetching rec only 'order by' is
done ie sorting is done after fetching. therefore using
'top' and 'order by' alone wont get max or min value. for
that we have to use 'distinct' before 'top' along with
'order by' (which will sort
the records before fetching).
select top 1 salary from employee where salary in (select
distinct
top n salary from employee order by salary desc)
| Is This Answer Correct ? | 3 Yes | 9 No |
BY Tested in Postgres SQL : javedcc@gmail.com
select * from tablename as aliasname where (nth)= (select
count(sal) from tablename where aliasname.sal<=sal)
| Is This Answer Correct ? | 8 Yes | 16 No |
Answer / ganesh prasad
all are not working properly incase repeat salary
this one is correct
select min(deptsal) from dept where deptsal in ( select
distinct top 4 deptsal from dept order by deptsal desc)
| Is This Answer Correct ? | 6 Yes | 14 No |
what are default? Is there a column to which a default cant be bound?
What are sql server procedures?
Explain the steps needed to create a scheduled job?
The external application that is executed in one of the tasks does not have a log file, but only a screen log. How can I save the data from the screen? : sql server management studio
What do you know about normalization and de- normalization?
What are the differences between local and global temporary tables?
How do you start single user mode in clustered installations?
What is trigger explain with program?
Can you please explain the difference between primary keys and foreign keys?
What is the difference between Clustered and Non-Clustered Index?
What function does a database engine serve in the sql server?
What is nolock?
Oracle (3253)
SQL Server (4518)
MS Access (429)
MySQL (1402)
Postgre (483)
Sybase (267)
DB Architecture (141)
DB Administration (291)
DB Development (113)
SQL PLSQL (3330)
MongoDB (502)
IBM Informix (50)
Neo4j (82)
InfluxDB (0)
Apache CouchDB (44)
Firebird (5)
Database Management (1411)
Databases AllOther (288)