Re: Finding Last 4 occurrences

James Reinardy <jreinardy_at_orion-corp.com> wrote in message news:383af2e3$0$96890_at_news.execpc.com...
> Hello all,
>
> I am having a mental block trying to get a query to work. I have work
> orders for part numbers, and I want to get up to the last four work
> orders for each part number.
>
> As an example, if I have the following data
>
> Order Part
> 1 A
> 2 A
> 3 A
> 4 A
> 5 A
> 6 A
> 7 B
> 8 B
> 9 B
>
>
> What I would like returned is:
>
> 1 A
> 2 A
> 3 A
> 4 A
> 7 B
> 8 B
> 9 B
>
> It seems like there should be a way, but I can't come up with it.
>
> Thanks for any help you can provide.
>
> Jim Reinardy,
> Orion Corporation

Using the Oracle example table,

SQL> select e1.empno, e1.deptno
  2 from emp e1, emp e2
  3 where e1.empno>=e2.empno
  4 and e1.deptno=e2.deptno
  5 group by e1.empno, e1.deptno
  6 having count(*)<=4
  7 order by e1.deptno, e1.empno;

    EMPNO DEPTNO
--------- ---------

     7782        10
     7839        10
     7934        10
     7369        20
     7566        20
     7788        20
     7876        20
     7499        30
     7521        30
     7654        30
     7698        30

11 rows selected. Received on Thu Nov 25 1999 - 09:47:47 CST