Raks

• Jun 27th, 2006

 

--Here the query to find out 10th Max salary

Table - Tbl_Test_Salary 
Column - int_salary

select max(int_salary) 
     from Tbl_Test_Salary 
            where int_salary in
               (select top 10 int_Salary from Tbl_Test_Salary order by int_salary)

Naresh Challagulla

• Jun 29th, 2006

 

Actually it should be:

select min(int_salary) from Tbl_Test_Salary 
      where int_salary in
               (select top 10 int_Salary from Tbl_Test_Salary order by int_salary desc)

 

Karthick Gunasekaran

• Jul 11th, 2006

 

Both the given answers might not suit if the table contains same salary for more than one person. In this case, we juz need to add DISTINCT when pulling the values - select min(salary) from (select distinct top 10 salary from employee order by salary desc) t1

jeya

• Jul 18th, 2006

 

Hi ,

As per your advice i used the above query to retrieve the top 10 values from a table in sybase.But unfortunately its throwing error "Incorrect syntax near the keyword 'top' ".Kindly let me know what is the problem with the below query.

Query:

select * from MKP_MKT_PRICE b where b.MKP_SEC_ID in (
select top 10 a.MKP_SEC_ID from MKP_MKT_PRICE a
where a.MKP_PRICE_DT='Jul 18 2006' order by a.MKP_MKT_PRC desc)

Raj

• Jul 25th, 2006

 

Hi,

select max(allowedamount)
     from tbl_InsuranceProceduresAmt
            where allowedamount in
               (select top 10 allowedamount from tbl_InsuranceProceduresAmt order by allowedamount desc)

This is perfect result

saxenaamit_mca

• Jul 25th, 2006

 

Find out nth highest salary from emp table
 SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

Enter value for n: 2
      SAL
---------
     3700

Gautam Kumar

• Jul 27th, 2006

 

TO find nth Salary

select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=&n

gautamsinha01

 Profile 

• Jul 27th, 2006

 

select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=&n

hani

• Aug 15th, 2006

 

hi,

the solution is :

SELECT TOP10 salary_field FROM employees_table ORDER BY salary_field DESC

mahi328

 Profile Answers by mahi328 Questions by mahi328

• Aug 17th, 2006

 

nth salary

select salary

from table_name a

where &n=(select count(salary) from table_name b where a.salary<=b.salary);

n salaries

select salary

from table_name a

where &n>=(select count(salary) from table_name b where a.salary<=b.salary);

csr21

 Profile Answers by csr21 Questions by csr21

• Sep 8th, 2006

 

select * from (select * from testsal order by losal desc)
where rownum<=10
minus
select * from (select * from testsal order by losal desc)
where rownum<10;

Ram Pratap Singh

• Sep 9th, 2006

 

select * from emp
where sal =  ( select sal from (select rownum rnum, sal
                                    from(select distinct sal from emp
                                           order by sal desc
                                         )
                                  )
                   where rnum = &salnum
             )

&salnum :  what position's sal

like 3,12,5,10

Raju.S

• Oct 3rd, 2007

 

1) Select * from ( select salary,DENSE_RANK() over(order by salary desc)
toprank from employee) where toprank=n

2) select * from employee a WHERE (n-1)= ( select count(*) from employee b
WHERE b.salary > a.salary )

Both the Queries are giving result for to find the 10 highest salary in SQL
query in the above queries n=10 for to find 10th highest salary.

PARDHA SARADHY NVS

• Oct 12th, 2007

 

Using TOP is possible only with MS SQL Server. This keywork TOP is not availbale in Oracle.

girishnavada1984

 Profile Answers by girishnavada1984 

• Mar 10th, 2009

 

SELECT TOP 1 salary FROM (SELECT TOP 10 salary FROM employee ORDER BY salary DESC)  A
ORDER BY salary

rohit1505

 Profile Answers by rohit1505 

• May 21st, 2011

 

SELECT *

FROM emp a

WHERE &n = (SELECT COUNT(DISTINCT sal))

FROM empb

WHERE a.emp<=b.emp);

yuvaevergreen

 Profile Answers by yuvaevergreen 

• Jun 14th, 2011

 

In Teradata, even if the duplicate salaries are present, below query can be used.

sel b.* from 

(

sel a.*,rank() over (order by a.salary desc) as rnk from

(sel salary  from samples.emp

group by salary ) as a

) b

where b.rnk=10