| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: how to find "bottom" of table in a group query
On Wed, 03 Mar 1999 14:10:17 +0200, Werner <werner.smit_at_face.co.zackityspam>
wrote:
>Consider table region_log:
> Name Null? Type
> ------------------------------- -------- ----
> AREA_CDE NOT NULL CHAR(4)
> TIMESTAMP NOT NULL DATE
> MODULE NOT NULL VARCHAR2(48)
> STAFF_ID_NO CHAR(13)
> ACTION VARCHAR2(48)
> REMARKS VARCHAR2(256)
>
>
>This is a log file.
>I use a select statement to see what is going on today by saying...
> select *
> from region_log
> where timestamp > sysdate-1
> order by area_cde, staff_id_no, timestamp
>
>But I would very much like to only see the last 10 transactions done by
>each staff_if_no.
>
>I thought there should be some easy way to do this. Bust everytime I
>attempt
>it the SQL statement grow so big that I give up on it.
>
>I was hoping for something like:
>select *
> from region_log r1
> where r1.timestamp = (Select max(timestamp)
> from region_log r2
> where timestamp > sysdate-1
> group by staff_id_no)
>
>But this cause error 1427:Single row subquery returns more than one
>row..
>Which I can understand.
>Is there an easy way of finding the last entry for each staff_id_no?
>
>And after finding the last entry , finding the 9 before that ?
You can do it with a index, a hint and a table with the staff_ids.
Consider...
SQL> create table emp as select * from scott.emp; Table created.
SQL> create table dept as select * from scott.dept; Table created.
SQL> select ename, deptno, hiredate
2 from emp
3 order by deptno, hiredate desc
4 /
ENAME DEPTNO HIREDATE
---------- ---------- --------- MILLER 10 23-JAN-82 KING 10 17-NOV-81 CLARK 10 09-JUN-81 ADAMS 20 12-JAN-83 SCOTT 20 09-DEC-82 FORD 20 03-DEC-81 JONES 20 02-APR-81 SMITH 20 17-DEC-80 JAMES 30 03-DEC-81 MARTIN 30 28-SEP-81 TURNER 30 08-SEP-81 BLAKE 30 01-MAY-81 WARD 30 22-FEB-81 ALLEN 30 20-FEB-81
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
First we want the last 5 people hired so we can first
SQL> create index hiredate_idx on emp( hiredate ); Index created.
And now if we hint the query with INDEX_DESC we get
SQL> select /*+ INDEX_DESC( emp hiredate_idx ) */
2 ename, to_char( hiredate, 'dd-mon-yyyy hh24:mi:ss' )
3 from emp
4 where hiredate > to_date( '01-JAN-0001', 'dd-mon-yyyy' )
5 and rownum < 6
6 /
ENAME TO_CHAR(HIREDATE,'DD
---------- -------------------- ADAMS 12-jan-1983 00:00:00 SCOTT 09-dec-1982 00:00:00 MILLER 23-jan-1982 00:00:00 FORD 03-dec-1981 00:00:00 JAMES 03-dec-1981 00:00:00
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=5 Bytes=125) 1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=5 Bytes=125) 3 2 INDEX (RANGE SCAN DESCENDING) OF 'HIREDATE_IDX' (NON-UNIQUE)(Cost=2 Card=5)
You can see that we do a index range scan descending on the hiredate_idx. You will also see that I put 'where hiredate > some real old date'. That is so the SQL parser will use our index and hint. Without it we would get
SQL> select /*+ INDEX_DESC( emp hiredate_idx ) */
2 ename, to_char( hiredate, 'dd-mon-yyyy hh24:mi:ss' )
3 from emp
4 where rownum < 6
5 /
ENAME TO_CHAR(HIREDATE,'DD
---------- -------------------- SMITH 17-dec-1980 00:00:00 ALLEN 20-feb-1981 00:00:00 WARD 22-feb-1981 00:00:00 JONES 02-apr-1981 00:00:00 MARTIN 28-sep-1981 00:00:00
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=1312)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=1312)
You can include other constraints also. Say you want the last two people hired in dept 10.
SQL> select /*+ INDEX_DESC( emp hiredate_idx ) */
2 ename, to_char( hiredate, 'dd-mon-yyyy hh24:mi:ss' )
3 from emp
4 where hiredate > to_date( '01-JAN-0001', 'dd-mon-yyyy' )
5 and deptno = 10 6 and rownum < 3
ENAME TO_CHAR(HIREDATE,'DD
---------- -------------------- MILLER 23-jan-1982 00:00:00 KING 17-nov-1981 00:00:00
So now we can ask for the last 2 people hired in each dept.
SQL> break on deptno
SQL> select e.deptno, e.ename, to_char( hiredate, 'dd-mon-yyyy hh24:mi:ss' )
2 from emp e, dept d
3 where e.empno in ( select /*+ INDEX_DESC( emp hiredate_idx ) */ empno
4 from emp 5 where hiredate > to_date( '01-JAN-0001', 'dd-mon-yyyy') 6 and rownum < 3 7 and deptno = d.deptno ) 8 and e.deptno = d.deptno
DEPTNO ENAME TO_CHAR(HIREDATE,'DD
---------- ---------- --------------------
10 MILLER 23-jan-1982 00:00:00
KING 17-nov-1981 00:00:00
20 ADAMS 12-jan-1983 00:00:00
SCOTT 09-dec-1982 00:00:00
30 JAMES 03-dec-1981 00:00:00
MARTIN 28-sep-1981 00:00:00
6 rows selected.
hope this helps.
chris.
>
>Thanks
>Werner
>
>PS:Sorry aboyt the subject heading : couldn't think of anything better.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |