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

What are “unrepeatable reads”?

0 Answers  

Define the one-to-one relationship while designing tables.

0 Answers  

Can a trigger be created on a view?

0 Answers  

What is spid in sql server profiler?

0 Answers  

How to create a view with data from multiple tables?

0 Answers  

How to select top 5 coloumn from a table without using coloumn name

5 Answers  

How do I repair damaged sql server mdf file database? In previous day my mdf file has got damage due to unknown reasons then I used dbcc chekcdb command but it failed, MDF file is important for me, I don’ know that how to get back mdf file data. Please anyone suggest me?

0 Answers  

How do you make a trace?

0 Answers  

Which command executes the contents of a specified file?

2 Answers   ABC, InfoMedia,

Does full backup break log chain?

0 Answers  

What are unicode character string data types in ms sql server?

0 Answers  

What is the difference between locking and multi-versioning?

0 Answers