How to find second highest value of a column in a table? - Page 3 - SQL Recipes a FREE cookbook of SQL queries and examples

#21 (**permalink**) 02-21-2008, 07:21 AM

SELECT b.field1

FROM (SELECT DISTINCT field1 FROM tab1) a,

(SELECT DISTINCT field1 FROM tab1) b

WHERE a.field1>=b.field1

GROUP BY b.field1

HAVING count(b.FIELD)=3

by changing the value (3) in last line you will get nth max value

#22 (**permalink**) 04-25-2008, 08:11 AM

Quote:

Originally Posted by ruby

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?

Ans : query is like this :

SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS E ORDER BY Salary ASC

#23 (**permalink**) 05-15-2008, 06:53 AM

SELECT * FROM product_master AS e1 

WHERE 

(N = 

(SELECT COUNT(DISTINCT (e2.prod_price))

FROM product_master AS e2 WHERE e2prod_price >= e1.prod_price)

)

where N is number you want

for example if you want 3rd highest than

SELECT * FROM product_master AS e1 

WHERE 

(3 = 

(SELECT COUNT(DISTINCT (e2.prod_price))

FROM product_master AS e2 WHERE e2prod_price >= e1.prod_price)

)

#24 (**permalink**) 05-19-2008, 11:40 AM

Quote:

Originally Posted by ashin

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

yes this will work ^_^

our sir gave us a hard way to find the 2nd highest value.

thax

#25 (**permalink**) 05-20-2008, 03:25 AM

SELECT max(column_value) FROM TABLE WHERE column_value<(SELECT max(column_value) FROM TABLE);

from
Mateti Kranthi Kumar

#26 (**permalink**) 05-20-2008, 11:00 AM

to find the third highest row in a table (employee_test for e.g.)

SELECT min(Emp_Sal) FROM Employee_Test WHERE Emp_Sal IN

(SELECT DISTINCT top 2 Emp_Sal FROM Employee_Test ORDER BY Emp_Sal DESC)

Vishal

#27 (**permalink**) 05-31-2008, 06:35 AM

SELECT top 10 column_name FROM table_name

#28 (**permalink**) 06-15-2008, 08:53 PM

Quote:

Originally Posted by ashin

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

Hi...
even I checked this out but could not understand what is happening. Can someone please explain to me what is happening here?
how the value '1' is used to get the second highest value???

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to find out the 4th highest value from the column?	Anoj biradar	SQL - Questions and Answers	3	12-05-2007 03:54 AM
How to find second highest value of a column in a table? \| SQL?	jaheer	SQL - Questions and Answers	9	07-24-2007 11:07 AM
how to find third highest record from table?	davinder singh	SQL - Questions and Answers	3	12-19-2006 11:57 AM

User Name		Remember Me?
Password