You can get any part of data and any sorted order you want with Analytic functions. Its syntax is OVER ((PARTITION BY … ORDER BY …)). Here is some samples with analytic functions I have used. First sample is getting number of (COUNT) employees by departments and ordered by (ROW_NUMBER ) departments. select A. DEPTNO , row_number () over (partition by A. DEPTNO order by A. EMPNO ) DEPT_EMP_ROWNO , A. EMPNO , row_number () over (order by A. DEPTNO , A. EMPNO ) EMP_ROWNO , count ( * ) over (partition by A. DEPTNO ) DEPT_TOTAL_EMP_CNT , count ( * ) over () TOTAL_EMP_CNT , count (distinct A. DEPTNO ) over () TOTAL_DEPT_CNT from SCOTT . EMP A order by ...