Ben

• May 9th, 2006

 

select max(salary) from tablename where salary not in (select salary from tablename order by salary desc limit n,q)

abdul rasheed

• May 19th, 2006

 

select ename,sal from ema where

&n=(select count (distinct(sal)) from empb where

a.sal<=b.sal;

webgurru

 Profile Answers by webgurru 

• May 30th, 2006

 

Query: SELECT sal FROM `emp` order by sal desc limit (n-1),1If the question: "how to display 4th highest (salary) record from customer table."The query will SELECT sal FROM `emp` order by sal desc limit 3,1

smitha m vincent

• Oct 30th, 2006

 

Query as:
select distinct(salary) from employee order by salary desc limit n-1,1
n->find the nth largest

to find 3rd largest query it as
select distinct(salary) from employee order by salary desc limit 2,1

Piyush Patel

• Sep 11th, 2007

 

Suppose two record are same for eg: two record has same value, than how we can find second highest record using same query ?

prisharma

 Profile Answers by prisharma 

• Dec 17th, 2007

 

Select sal From emp X Where n =

( Select Count(Distinct sal) From emp Where sal >=X.sal )



YOu can change the value of n and get the 4th , 5th.... sal of the employee...

vishakhaw

 Profile Answers by vishakhaw 

• May 12th, 2008

 

select DISTINCT(salary) from table_name order by salary desc limit 3,1

Kvgravindra

 Profile Answers by Kvgravindra 

• Jul 1st, 2008

 

select * from emp where rownum =1 and rowid not in
(select rowid from emp where rownum < 4);

Kvgravindra

 Profile Answers by Kvgravindra 

• Jul 1st, 2008

 

I got the answer using this query, but seems lengthy. Can anybody suggest shorten this??

for nth highest sal from emp table

Code
 SELECT * FROM (SELECT * FROM (SELECT * FROM emp ORDER BY sal) WHERE rownum <=n ORDER BY sal desc) WHERE rownum = 1 ;


 

Kvgravindra

 Profile Answers by Kvgravindra 

• Jul 2nd, 2008

 

Code
 SELECT e1.ename,e1.empno,e1.sal FROM emp e1 WHERE n-1=(SELECT count(DISTINCT sal) FROM emp e2 WHERE e2.sal>e1.sal)


 

Try this

quick

 Profile Answers by quick 

• Jul 17th, 2008

 

FOR ORACLE DATABASE

Code
SELECT rownum,sal 


FROM (SELECT sal 


FROM emp


ORDER BY sal desc)


WHERE rownum < 5


 

FOR POSTGRES I AM ABLE TO FIND HIGHEST 4 RECORDS:

Code
SELECT emp_salary FROM salary_detail 


ORDER BY emp_salary desc


LIMIT 4

BUT I AM NOT ABLE TO GET THE FOURTH HIGHEST RECORD, CAN ANY BUDDY PLEASE HELP

THANKS IN ADVANCE

pola_sandeep

 Profile Answers by pola_sandeep 

• Sep 24th, 2008

 

select max(sal) from emp where sal not in
(select top 1 sal from emp group by sal order by sal desc)
--This displays 2nd highest salary if you want to display 5th highsest record then replace top 1 with top 4 for the N th record top N-1

cnikita

 Profile Answers by cnikita 

• Nov 10th, 2008

 

Code
SELECT b.salary FROM 


(SELECT DISTINCT salary FROM emp) a,


(SELECT DISTINCT salary FROM emp) b


WHERE a.salary >= b.salary


GROUP BY b.salary


HAVING COUNT(b.salary) = 3;


 

Code
SELECT salary FROM emp e WHERE 2 = (SELECT count(DISTINCT(salary)) FROM emp d WHERE e.salary < d.salary ) ORDER BY salary asc

zia007

 Profile Answers by zia007 

• Nov 20th, 2008

 

Code
SELECT max(salary) FROM emp WHERE salary<(SELECT max(salary) FROM emp)


 

Elayaraja

• Jul 27th, 2011

 

Its very easy to find Nth largest value in column in table.

Code
SELECT DISTINCT user_id FROM `tbl_general` ORDER BY user_id DESC LIMIT (n-1),1

saiprathapreddy

• Aug 23rd, 2011

 

select column_name from table_name order by column_name desc limit 3;

eg;select sal from emp order by sal desc limit 4;

lucky

• Aug 28th, 2011

 

Code
SELECT max(salary) FROM empolyee e1 WHERE 4<(SELECT count(*) FROM employee e2) WHERE e1.salary <e2.salary

satish

• Sep 9th, 2011

 

To get 4th highest sal:

Code
SELECT top 1 sal FROM sal 


WHERE sal IN (SELECT top 4 * FROM sal


ORDER BY sal DESC)


ORDER BY sal ASC

mahendra pandey

• Sep 12th, 2011

 

This is the second highest salary .
you can find third and fourth highest record as well as.....

Code
<?php


$conn=mysql_connect("localhost","root","") or die(mysql_error());


mysql_select_db("test",$conn) or die(mysql_error());


$sql1 = "select salary from emp group by salary order by salary desc Limit 1,1";


$sql3="select name,salary from emp where salary=(".$sql1.")";


$rs3 = mysql_query($sql3) or die(mysql_error());


$res=mysql_fetch_array($rs3);


$sal=$res['salary'];


echo $sal;


$s="select max(salary) from emp where salary <'$sal'";


$sr=mysql_query($s)or die(mysql_error());


$out=mysql_fetch_array($sr)or die(mysql_error());


print_r($out);


?>

Hariharan Subramoniam

• Sep 16th, 2011

 

Code
SELECT name, salary


FROM table


WHERE salary < ( 


SELECT max( salary ) 


FROM table ) 


ORDER BY salary DESC 


LIMIT (n-1) , 1

YASHPAL CHAHAR

• Sep 17th, 2011

 

Code
SELECT * FROM EmployeeInfo as e1 WHERE n=(SELECT count(*) FROM EmployeeInfo as e2 WHERE e1.salary <= e2.salary );

where n is number that you want to find out which highest salary.

Santosh

• Sep 21st, 2011

 

Code
SELECT MAX(salary) FROM employment LIMIT 3,1

Jit

• Sep 28th, 2011

 

The answer is...

Code
SELECT `Salary` FROM `customer`ORDER BY `Salary` DESC  LIMIT 5,1

fahim

• Mar 31st, 2012

 

If the highest column is salary, you do this:

Code
SELECT salary FROM employee


ORDER BY salary desc


LIMIT 2


 

Zafar Malik

• Apr 21st, 2012

 

4th higest salary :

Code
SELECT a.salary FROM customer a WHERE 3=(SELECT count(b.salary) FROM customer b WHERE b.salary>a.salary)

Bhaskar

• Jul 7th, 2012

 

Code
SELECT MIN(salary) 


FROM Customer


WHERE salary IN


(


SELECT TOP 4 salary


FROM Customer


ORDER BY salary DESC


)

Sathish Kumar

• Jul 10th, 2012

 

ITS a basic query, then just use LIMIT:
-- get the 4th highest salary

Code
SELECT salary FROM customer


ORDER BY salary DESC


LIMIT 4,1

Vinit

• Sep 13th, 2012

 

Code
SELECT salary FROM table_name ORDER BY salary DESC LIMIT 1 OFFSET 3 

sgarach

 Profile Answers by sgarach 

• Dec 28th, 2012

 

SELECT salary FROM customer
ORDER BY salary DESC
LIMIT 3,1

Nadeem

• Apr 2nd, 2013

 

SELECT emp_salary FROM salary_detail ORDER BY emp_salary desc LIMIT 3,1

Manoj Pandey

 Profile Answers by Manoj Pandey 

• Nov 26th, 2013

 

Above query is right but we should use also Group by because many employee can be same salary.

SELECT emp_salary FROM salary_detail GROUP BY emp_salary ORDER BY emp_salary desc LIMIT 3,1

mukul Bhardwaj

• May 17th, 2014

 

SELECT max(salary) FROM `testtable` where salary <(SELECT max(salary) FROM `testtable` where salary <(SELECT max(salary) FROM `testtable` where salary <(SELECT max(salary) FROM `testtable` having salary < max(salary)))) limit 1

Code
 

Arun kumar yadav

• Nov 13th, 2014

 

SELECT DISTINCT SALARY FROM TABLENAME ORDER BY DESC SALARY LIMIT 3,1;

raj kumar saw

• Aug 2nd, 2015

 

Yes, it is. But this answer is not the best approach.

Fawwad

• Feb 26th, 2016

 

Code
SELECT * FROM SALARY ORDER BY SALARY DESC LIMIT N-1,1

anish

• May 11th, 2016

 

Code
SELECT * FROM ( SELECT * FROM order1 ORDER BY o_id LIMIT 5 ) AS T ORDER BY o_unit DESC LIMIT 3;

Hitender

• Jan 27th, 2017

 

SELECT DISTINCT(`salary`) FROM `customer` ORDER BY `salary` DESC LIMIT 3,1;

Dan Rusk

• Apr 30th, 2017

 

Code
SELECT *


FROM (


       SELECT


         c.*,


         @rownum := @rownum + 1 AS rank


       FROM customer c,


         (SELECT @rownum := 0) r


       ORDER BY c.salary DESC


     )


     t


WHERE t.rank = 4;

Amith L

• Jul 11th, 2017

 

You can find out nth highest salary,
In the below code just change the where condition i.e., if you want to get 2nd highest salary, use where (2)

Code
SELECT * FROM CUSTOMER CUS1 


WHERE (4) = 


(SELECT COUNT(CUS2.SALARY) FROM CUSTOMER CUS2 TABLE


WHERE CUS2.SALARY >= CUS1.SALARY)