Answer / seeman

select * from table_name t1 where (n-1)=
(select count(distinct(t2.column_name)) from table_name t2
where t2.column_name>t1.column_name)

Answer / babu

select * from emp e where &num=(select count(distinct(sal))
from emp b where b.sal>=e.sal)

Answer / arun

SELECT MAX(salary)FROM employee WHERE salary NOT IN
(SELECT TOP 1 salary FROM employee ORDER BY salary DESC)

Answer / jiten

select top 1 salary from employee where salary in (select
top n salary from employee order by salary desc)

Answer / yogesh

select e2.salary
from emp e1
join emp e2
on e1.salary >= e2.salary
group by e2.salary
having count(e2.salary) = n

Answer / bhanu prakash

SELECT TOP 1 * FROM (SELECT TOP N * FROM EMPSAL ORDER BY ID DESC) A ORDER BY ID

Answer / harninder

select `salary` from test t1 where 0=(select count(*) from
test where t1.`salary`<`salary`)

Answer / usha

SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY
DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = n

n may be 2,3....

Answer / deven

<font color="Green" >This is long method but it works</color>

<font color="red" >step 1(this will make salary in
ascending order)</color>

<font color="blue" >create index Ind_sal on emp(salary)</color>

<font color="red" >step 2(to calculate Nth highest salary
where N=1,2,3,4.....)</color>

<font color="blue" >select distinct top N salary from emp
except
select distinct top N-1 salary from emp
</color>

<font color="red" >in some databases 2nd step may be</color>
<font color="blue" >
select distinct top N salary from emp
minus
select distinct top N-1 salary from emp</color>

Answer / suprotim agarwal

CREATE TABLE #Employees (EmpID int, EmpName varchar(10),
Salary int)
INSERT #Employees SELECT 1,'Tim',345345
INSERT #Employees SELECT 2,'Jill',76845
INSERT #Employees SELECT 3,'Kathy',234545
INSERT #Employees SELECT 4,'Jack',74564
INSERT #Employees SELECT 5,'Harry',56756456
INSERT #Employees SELECT 6,'Arnol',75675
INSERT #Employees SELECT 7,'Dave',76566
INSERT #Employees SELECT 8,'Zinnade',58776
INSERT #Employees SELECT 9,'Iflar',345567
INSERT #Employees SELECT 10,'Jimmy',76766

Highest Salary Of an Employee

SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY
DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 1

2nd highest salary


SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY
DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 2

How get current date in SQL server 2000

6 Answers   Cap Gemini,

What is used to replicate sessions between instances in coldfusion clusters?

0 Answers   HCL,

How to a Query to copy data from on table to another table.

6 Answers   Cap Gemini,

how to know Who Is Blocking Your SQL Server?

4 Answers  

What is the use of SCOPE_IDENTITY() function?

2 Answers  

Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases?

2 Answers  

Are resultset updatable?

0 Answers  

Explain the use of keyword with encryption. Create a store procedure with encryption?

0 Answers  

Explain the various types of concurrency problem. I.e. Lost or buried updates, uncommitted dependency, inconsistent analysis, phantom read?

0 Answers  

What is the difference between mysql and sql server?

0 Answers  

How to handle error or exception in sql?

0 Answers  

How to change the name of a database user?

0 Answers