This content has been marked as final. Show 2 replies
This question doesn't concern SQL*Plus or iSQL*Plus in any way, does it? It's a SQL question, so, in the future, you might have better luck posting questions like this in the SQL forum:
The analytic RANK (or, depending on how you want to handle ties, ROW_NUMBER) function is more versatile than aggregate functions for this kind of thing
Since I don't have a version of your table, I'll use the scott.emp table to illustrate.
Here's one way to get some information about the two latest rows (in order by hiredate) for each job:
WITH got_r_num AS ( SELECT job, hiredate, ename, sal , RANK () OVER ( PARTITION BY job ORDER BY hiredate DESC ) AS r_num FROM scott.emp ) SELECT * FROM got_r_num WHERE r_num <= 2 ORDER BY job , hiredate DESC ;
JOB HIREDATE ENAME SAL R_NUM --------- --------- ---------- ---------- ---------- ANALYST 19-APR-87 SCOTT 3000 1 ANALYST 03-DEC-81 FORD 3000 2 CLERK 23-MAY-87 ADAMS 1100 1 CLERK 23-JAN-82 MILLER 1300 2 MANAGER 09-JUN-81 CLARK 2450 1 MANAGER 01-MAY-81 BLAKE 2850 2 PRESIDENT 17-NOV-81 KING 5000 1 SALESMAN 28-SEP-81 MARTIN 1250 1 SALESMAN 08-SEP-81 TURNER 1500 2
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
Explain, using specific examples, how you get those results from that data.