Tuesday, August 31, 2010

row_number over partition by

select *
  from (select deptno, ename, sal,
          row_number() over (partition by deptno
                             order by sal desc)
          rn from t)
    where rn <= 2


this query can perform triple functionality
Partition by :- divide the data based on deptno
order by :- it applier order by only on the sal for each dept no.
rn :- no of rows for each dept

No comments:

Post a Comment