how to find out second highest salaried employee details of a table in sqlserver

with s1(id,names,sal) as (select 1 ,'a' ,1.00 union all select 2,'b',2.00 union all select 3,'c',3.00 union all select 4,'d',4.00 union all select 5,'f',5.00)

select * into sam from s1

select * from sam

--get 2nd highest sal.emp

• select top 1 * from sam where sal in (select top 2 sal from sam order by sal desc) order by sal


• select * from sam tbl1 where 1=(select count(distinct sal) from sam tbl2 where tbl1.sal<tbl2.sal)


• select top 1 * from ( select top 2 * from sam order by sal desc)s order by sal


• select * from (select *, rn=row_number() over(order by sal desc) from sam) s where rn=2


• select * from (select *, rn = dense_rank() over(order by sal desc) from sam) s where rn=2

--get 2nd lowest sal.emp

• select top 1 * from sam where sal in (select top 2 sal from sam order by sal) order by sal desc

 

Max possible columns per a table in sqlserver is 1024 but in oracle 1000
Max possible databases : 32,767