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

SQL:

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

Ans : query is like this :

SQL:

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

SQL:

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

SQL:

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)

)

yes this will work ^_^

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

thax

SQL:

select max(column_value) from table where column_value<(select max(column_value) from table);

from
Mateti Kranthi Kumar

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

SQL:

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

Re: How to find first ten records from table

Dialect: ANY

SQL:

Select top 10 column_name from table_name

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???

This is how you can achieve this using the RANK function(T-SQL).

SELECT * FROM
(SELECT clientname,quantity, RANK() OVER (PARTITION BY clientname ORDER BY quantity) AS rn FROM trade) as a
WHERE a.rn < 3 AND a.rn > 1

Do try this and I am sure it will work.

-SPK

try this.......

select MIN([Numberofinvites]) from
(select top 1 * from [event] ORDER BY [Numberofinvites] desc) ss

the logic behind this is first we should have to make the column in desc order then select top 2 values and from those top 2 value select the min value... this is very helful when the table contain more enough values.....

Please post back if u r satisfied with this

thanks
Arjun