How to find second highest value of a column in a table?

Dialect: MySQL
I have a table - Trade with 2 columns -Client name and quantity.
I want to find out the name of the client with second highest quantity. How do I proceed?

Dialect: MS SQL

SQL:

SELECT * FROM table

WHERE columnName = 

( SELECT MAX(columnName) FROM table where columnname<(select max(columnname) from table))

SQL:

SELECT TOP 1 quantity  FROM Trade

  WHERE quantity < (SELECT MAX(quantity)  FROM Trade)

  ORDER by  quantity DESC;

Is this query will work? If it works what should be the output?

regards,
venkat

Dialect: Oracle

SQL:

SELECT quantity   FROM Trade WHERE  quantity=(SELECT MAX(quantity)

FROM Trade WHERE  quantity<> (SELECT MAX(quantity) FROM  Trade))

Dialect: MySQL

SQL:

SELECT Max(quantity) FROM Trade

WHERE quantity Not In (SELECT MAX(quantity) FROM Trade);

Hi..is it possible to write the same query without using top & max...
can anyone tell me??

Yes it is possible to answer by using correlated queries.

SQL:

select sal from emp e where 1=(select count(*) from emp where e.sal<sal);

This is a cheap query written..............asdfadfaaf

This will get you only the second max salary row

SQL:

SELECT * FROM EMP

WHERE SAL IN (SELECT MAX(SAL) FROM EMP 

WHERE SAL <> (SELECT MAX(SAL) FROM EMP)) 

from how to find second largest number through a query in sql - Community Forums - Developer Fusion, the UK developer community - VB, ASP, C#, .NET, PHP and XML tutorials & source code

SQL:

select * from tmp_table m1

where (n-1)=(select count(distinct(m2.age))

from tmp_table m2 where 

m2.age>m1.age)

Hello can any buddy suggest me how to get the 3rd highest value from table and can ve right any query to get 4th, 5th as well?

For finding 2'nd largest row , we can use various forms ..like
(1)

SQL:

select max(value1) from ( select value1 from val minus select max(value1) from val );

(2)

SQL:

Select max(value1) from val where value1 < ( Select max(value1) from val );

(3)

SQL:

select max( value1) from val where value1 not in( select max(value1) from val );

But there is 1 General way also..we can simply use this formula--

SQL:

select rownum,ename,eno from emo gropu by rownum,ename,eno having rownum > n minus select rownum, ename ,  eno from emp group by rownum,ename,eno having rownum > n ;

cheers...

Second highest value could be find out by entering this command:-
Let say u want to find out salary(second highest) from employ table then..

SQL:

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

Regards,
Mohammed Obaid Khan
Pune, Maharashtra
India

make use of MINUS operator
supposing to find second highest sal from tablename(table)
ie

SQL:

select max(sal) from( select sal from tablename

                                     MINUS

                                     select max(sal) from tablename)

Do You Know How to select the nth(for ex : 10th) largest number in a column without using max() function..
I am using orderid column from orders table in nortwind database for example

SQL:

select top 1 * from(select top 10 orderid from orders order by orderid desc)a order by orderid asc

By : Krishna Prasad J
krishnaprasad.jk@gmail.com

SQL:

select * from tablename orderby salary  limit 1 offset 2

use 3 for third highest 4 for 4th highest after offset

sameerargade@gmail.com

Hi,
You can use the following:

SQL:

SELECT *

      FROM (SELECT ename

                         ,enumber

                         ,sal

                         ,ROW_NUMBER() OVER(ORDER BY sal DESC) rn

                  FROM emp)

          WHERE rn < 3;

Also, you can use the DENSE_RANK() functionality to get this done in a better way:

SQL:

SELECT * 

      FROM (SELECT ename

                         , sal

                         , Dense_Rank() over(ORDER BY sal DESC

                                                     )rn 

                  FROM emp) 

      WHERE rn < 3;

Thanks,
vinay singh
Sagarpur New Delhi, India
email- vinay_1020@live.in

Re: how to display the first ten records?

Dialect: ANY
i have an employe table. i want display the first ten records.how?

SQL:

select quantity from trade a where 2=(select count(quantity) from trade b where a.quantity<=b.quantity);

-hello friend this query is called nested sub query this is the only way to find the values in given count order

You can use following query to find any highest from a table column values:

SQL:

SELECT MIN(columnName) 

FROM tableName  WHERE 

columnName IN (SELECT DISTINCT TOP N columnName FROM tableName ORDER BY columnName DESC);

Correlated query:

1.

SQL:

select sal from emp e where 1=(select count(distinct(sal)) from emp where e.sal<sal);

this gives the exact second heighest value

2.

SQL:

select sal from emp e where &(n-1)=(select count(distinct(sal)) from emp where e.sal<sal);

this gives the nth heighest value from atable


k.lakshmi_narayana@yahoo.co.in

How can find the rows excluding the top 2 highest values

Dialect: ANY
this is how you can simply do this

SQL:

select col_name from table_name where col_name not in (select col_name from (select col_name from table_name where col_name is not NULL order by col_name desc)

where rownum<=2)order by col_name

you can use this and this is general you can find any highest value

SQL:

select min(sal) from (select sal from emp where sal is not NULL order by sal desc) where rownum<=&rownum

Re: Get Max Salary at any rank

Dialect: ANY

SQL:

select salary from(

    select a1.salary,count(a2.salary)sal 

    from salarytable a1,salarytable a2

    where a1.salary<=a2.salary or (a1.salary=a2.salary) 

    group by a1.salary)a where sal=4   

Here 4 specifies Max salary at fourth position