This content has been marked as final. Show 2 replies
Welcome to the forum!
Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements), and the results you want from that data. Explain how you get the resutls you want from that data.
If you can use commonly available tables (like those in the scott schema) to show your problem, then you don't have to post the sample data; just post the results you want from that data and the explanation.
For example, I think you want to do something very much like this:
"I want to show only 3 rows from each department in the scott.emp table, the 3 rows rows with the latest hiredates. If there happens to be a tie (2 or more people in the same department with the same hiredate), then I want the one with the first ename (in alphabetic order) to be considered later than the others. The reuslts I want are:
` DEPTNO ENAME HIREDATE
---------- ---------- ---------
10 MILLER 23-JAN-82
10 KING 17-NOV-81
10 CLARK 09-JUN-81
20 ADAMS 23-MAY-87
20 SCOTT 19-APR-87
20 FORD 03-DEC-81
30 JAMES 03-DEC-81
30 MARTIN 28-SEP-81
30 TURNER 08-SEP-81
I'm using Oracle 10.2.0.1.0."
Here's one way to do that:
This is called a Top-N Query , because you want N items (N=3 in this case) from the top of a sorted list.
WITH got_r_num AS ( SELECT deptno, ename, hiredate , ROW_NUMBER () OVER ( PARTITION BY deptno ORDER BY hiredate DESC , ename ) AS r_num FROM scott.emp ) SELECT deptno, ename, hiredate FROM got_r_num WHERE r_num <= 3 ORDER BY deptno , hiredate DESC , ename ;
The query above will work in Oracle 9 (and up).
Edited by: Frank Kulash on Feb 23, 2011 7:41 AM