-- Top N Salary 
SELECT * 
FROM Employee Emp1
WHERE (N-1) = ( 
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

 -- Top 3 Salaries 

 SELECT a.ename, b.sal
    FROM emp a, emp b
    WHERE a.empno = b.empno
          AND
          3 > (SELECT count(*) FROM emp b
                   WHERE a.sal = b.sal);

 SELECT distinct
  emp
      ,Max(CASE timings    WHEN 'I' THEN  coalesce(swipe,cast('00:00:00' as time(2)))     END ) over ( partition by emp order by swipe rows between 1 preceding  and 1 following ) AS Timeou
    ,Max(CASE  timings        WHEN 'O' THEN  coalesce(swipe,cast('00:00:00' as time(2)))    END )  over (partition by emp order by swipe  rows between 1 following  and 1 following )  AS Timeo
,timeou-timeo day(4) to hour AS TIME_DIFF
FROM (sel emp ,swipe, case when ROW_NUMBER()  OVER (partition by emp ORDER BY swipe)   MOD 2  = 0  then 'O' else 'I'   end  as Timings 
from swipe)  a qualify timeo is not null 

 simple column to row conversion

 select name , sum(case subject when 'English' then marks end )as english
,sum(case subject when 'Maths' then Marks end )as Maths
,sum(case subject when 'Science' then marks end )as Science from col2row group by 1
--create table currency (name varchar(10), value integer , date1 date);

 --insert into  currency values ('INR',300,'01-Jun-2015')

 --create table ord ( id integer , ordval integer , orderdate date, orgvalue integer)

 --insert into ord values ( 1,20,'05-jun-2015',null)
--create table currency ( id integer,value integer , date1 date);

 --insert into currency values ( 1,100,'01-Jan-2015');

 --select * from currency;

 --delete from currency where  VALUE =100;

 --create table ord ( ordid integer , id integer , ordval integer , purdate date);

 --insert into ord values ( 1 , 1, 200 , '05-Mar-2015');

 --select * from ord
--case when b.value is null then (select ordval from ord )

 select  id,value , case when b.value is null then (select
Max(b.value) over (partition by b.id , b.value  order by b.date1 rows between unbounded preceding and 1 following ) from currency )end as lastvalue
from 
currency b where date1='01-Jun-2015'

 select id,
Max(b.value) over (partition by b.id   order by b.date1 rows between unbounded preceding and unbounded following  ) from currency b where date1='05-Mar-2015' group
by 1 

 --select id, value , 

 select a.ordid , a.ordval , purdate,b.value , case when b.value is null then (select
Max(b.value) over (partition by b.id , b.value  order by b.date1 rows between 1 preceding and 1 preceding  ) from currency b where date1='01-Jun-2015'  )end as lastvalue
--last_value(b.value ignore nulls) over (partition by b.id , b.value ,order by date1 rows between unbounded preceding and 1 preceding ) as lastvalue 
 from ord a  left outer join currency b on a.id=b.id and date1='01-Jun-2015'


 --executed query--
select a.ordid , a.ordval , purdate,b.value, case when b.value is null then (select date1 from currency
where date1 in (select  max(date1) from currency where value is not null  and date1 < purdate )  )end as  date2 ,
case when b.value is null then (select value from currency
where date1 in (select  max(date1) from currency where value is not null  and date1 < purdate )  )end as lastvalue
--last_value(b.value ignore nulls) over (partition by b.id , b.value ,order by date1 rows between unbounded preceding and 1 preceding ) as lastvalue 
 from ord a  left outer join currency b on a.id=b.id and date1=purdate