Re: Finding Last 4 occurrences

Benjamin,

First of all, you missed the ORDER BY work_order DESC in your subselect, because James want to know the LAST four work orders. But even if you use the ORDER-clause, you cannot be sure that the query will produce the correct resultset, because oracle will choose the four last rows BEFORE Sorting the resultset by descending work_order!

The thing is, that you have to force oracle to sort before choosing the 4 last rows. This might be done by a join on work_order. All this is a kind of 'Top Ten Problem' which is not easy to solve in Oracle. Using a synchronized subquery might be slow on huge tables.

Use something like this:  

SELECT work_order
  FROM t1,

       DUAL
 WHERE -1*work_order = DECODE(DUAL.dummy(+),'X',NULL,NULL)    AND rownum <= 4
  ORDER BY t1.work_order DESC;

Now you can use the resultset as subselect to join the part_no. The join with DUAL seems to be stupid, but it's only to force oracle to sort by work_order as mentioned above. I used this technique in an application last year and in my opinion it's the fastest solution of this problem.

James, please be careful, I didn't test the query, maybe you have to correct the syntax....

Hope it works.

Jens

On Tue, 23 Nov 1999 20:38:53 -0600, "Benjamin R Heath" <benheath_at_uswest.net> wrote:

>I think this will get it, assuming your table is t1
>
>select work_order, part_number
>from t1 a
>where work_order in
>(select work_order
> from t1
> where part_number = a.part_number
> and rownum <= 4
>)
>
>James Reinardy wrote in message <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
>
>

-
Jens Mayer
Rat & Tat GmbH
Hamburg, Germany Received on Wed Nov 24 1999 - 06:51:46 CST