Answer / himanshu

Just replace the N with required number:


SELECT empid, empname, empsalary FROM emp e
WHERE (SELECT COUNT(DISTINCT(e2.empsalary))
FROM emp e2
WHERE e2.empsalary >=
e.empsalary ) = N

ex: 4th highest

SELECT empid, empname, empsalary FROM emp e
WHERE (SELECT COUNT(DISTINCT(e2.empsalary))
FROM emp e2
WHERE e2.empsalary >=
e.empsalary ) = 4

Answer / aravinda

try with this query you will get desired position salary.


Select Salary From Employees a Where 1=(
Select Count(Distinct Salary) From Employees b
Where a.Salary <= b.Salary );

just replace where clause number for desired position. ,2,3,4

Answer / sanchit dubey

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

Answer / avinash

declare @n;
set @n = N
select * from tbl
where @n in (
select dense_rank over (order by salary) as rank_sal
from tbl )

Answer / harshal

Below query is written assuming table "employee" has a column called "salary".
select salary from (select salary from (select distinct(salary) from employee order by salary desc)where rownum<n) order by salary asc where rownum<1;

Answer / yoursguna@gmail.com

SELECT * FROM `salary_table` group by `salary` order by
`salary` desc limit 1,1

Answer / nitin munjani

Using Row_Number() function

Here N refers the Nth highest salary

select empid,empname from mstemployee where empsalary in (
(select empsalary from
(select Row_Number() over(order by empsalary desc)
rownm,empsalary from mstemployee
group by empsalary) o where rownm=N))

Answer / vinay upadhyay

These are tested queries to find Nth highest salary

Select distinct(sal) From employee emp Where n =
( Select Count(Distinct y.sal) From employee y Where y.sal
>=emp.sal)

OR

select distinct(salary) from employee order by salary desc
limit n-1,1

Answer / ravindra babu

select sal from (select sal from(Select distinct sal from
EMP order by sal desc)Where rownum <=2 order by sal asc)
Where rownum=1;

Answer / sudhir

DISPLAY THE RECORDS WHO'S GETTING 3RD HIGHEST SALARY.

SELECT * FROM EMP E1
WHERE 3 =(SELECT COUNT(DISTINCT(SAL))
FROM EMP E2
WHERE E1.SAL <= E2.SAL)

Assume,there are three tables in a database, Can i have both primary key and foreign key for each table in the same database?

4 Answers  

1. What are the grouping function in SQL ? 2. If base table of a view deleted means, what will happen while we querying on view ? will give any error ? 3. Difference between DROP, DELETE, TRUNCATE table ? 4. What is constraints and types ? 5. What is max size of nchar & nvarchar ? 6. Define ROLLBACK, COMMIT, SAVE POINT 7. How non-clustered better ? or rank the Clustered, Non-Clustered and Table scan in performance wise 8. Select 10 rows from a table ? 9. Define DML, DDL, DCL, DTL commands ? 10. What is mean by NULL value ? NULL means "" or 0 or undefined ? 11. Default constraints ? 12. Can we have more then primary Key in table ? 13. Type of integrity ? Entity, Referential, Domain ?

10 Answers   Perot Systems,

What is mean by clustered index and non clustered index, give syntax of creation? : sql server database administration

0 Answers  

What is analysis service repository?

0 Answers  

Do you know concepts and capabilities of sql server?

0 Answers  

How to apply filtering criteria at group level with the having clause in ms sql server?

0 Answers  

What are translations and its use? : sql server analysis services, ssas

0 Answers  

sql database suspect We have a sql database that is showing as suspect. How can we recover?

0 Answers  

How get current date in SQL server 2000

6 Answers   Cap Gemini,

Is it possible to update the views? If yes, how, if not, why?

0 Answers  

Whether the updations made to the views reflect the original records in the table

5 Answers   NIIT,

What is the difference between a "where" clause and a "having" clause?

0 Answers