Hi Guys,

I have a very strange problem with "where My_ID in (Select My_ID
from ...)

I am trying to keep only the most recent 10 Invoices for each client
in the History Table.

So I execute:

SELECT A.Unique_ID
FROM HISTORY_INVOICES A
WHERE A.Unique_ID NOT IN (SELECT FIRST(10) B.Unique_ID
FROM HISTORY_INVOICES B
WHERE B.CLIENT_CODE = A.CLIENT_CODE
ORDER BY B.Unique_ID DESC);

So it should select all but the first 10 records from
HISTORY_INVOICES
where exists more than 10 records for that Client_Code.


IT DOES COME BACK NULL!!!!!!


However if I run:

SELECT A.Unique_ID
FROM HISTORY_INVOICES A
WHERE A.Unique_ID NOT IN (1,2,3,4,5,6,7,8,9,10);

It works fine, and it retrieves let's say 11,12,13

Any ideas why?

Cheers,
Fabian