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?

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

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.

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
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
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)
select max(value1) from ( select value1 from val minus select max(value1) from val );
(2)
Select max(value1) from val where value1 < ( Select max(value1) from val );
(3)
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--
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..

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 select max(sal) from( select sal from tablename
MINUS
select max(sal) from tablename)