find 2nd highest value in a table

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ramakrishna-423492 SSC Journeyman Points: 99 More actions · Answer 1

select top 1 * from (select top n * from dbo.emp order by sal desc) a order by sal

😀

Ramakrishna-423492 SSC Journeyman Points: 99 More actions · Answer 2

You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP 1 salary

FROM (

SELECT DISTINCT TOP n salary

FROM employee

ORDER BY salary DESC) a

ORDER BY salary

where n > 1 (n is always greater than one)

😉

Ramakrishna-423492 SSC Journeyman Points: 99 More actions · Answer 3

Create table tmp_table (name varchar(20),age int)

Insert into tmp_table Values('Hari',27)

Insert into tmp_table Values('Sai gopal',27)

Insert into tmp_table Values('Jeeva',29)

Insert into tmp_table Values('Shanmuga Raja',25)

Insert into tmp_table Values('Senthil',26)

Insert into tmp_table Values('Sandesh',27)

Insert into tmp_table Values('Segu',25)

)

Select * From tmp_table

select * from tmp_table m1

where (0)=(select count(distinct(m2.age))

from tmp_table m2 where

m2.age>m1.age)

--Drop table tmp_table

j nuñez SSCrazy Points: 2175 More actions · Answer 4

Try this

SELECT TOP 1 YourField

FROM

(SELECT TOP 2 YourField

FROM TABLE

ORDER BY YourField DESC) AS DATA

ORDER BY YourField