Hi Fabian!

Don't ever use FIRST within a subselect, it simply does not work!

Rather, do something like:

SELECT A.Unique_ID
FROM HISTORY_INVOICES A
WHERE (SELECT Count(*)
FROM HISTORY_INVOICES B
WHERE B.CLIENT_CODE = A.CLIENT_CODE
AND B.Unique_ID < A.Unique_ID) > 10

or

SELECT A.Unique_ID
FROM HISTORY_INVOICES A
WHERE EXISTS(SELECT * FROM HISTORY_INVOICES B1
JOIN HISTORY_INVOICES B2 ON B2.CLIENT_CODE = B1.CLIENT_CODE
AND B2.Unique_ID < B1.Unique_ID
JOIN HISTORY_INVOICES B3 ON B3.CLIENT_CODE = B1.CLIENT_CODE
AND B3.Unique_ID < B2.Unique_ID
JOIN HISTORY_INVOICES B4 ON B4.CLIENT_CODE = B1.CLIENT_CODE
AND B4.Unique_ID < B3.Unique_ID
JOIN HISTORY_INVOICES B5 ON B5.CLIENT_CODE = B1.CLIENT_CODE
AND B5.Unique_ID < B4.Unique_ID
JOIN HISTORY_INVOICES B6 ON B6.CLIENT_CODE = B1.CLIENT_CODE
AND B6.Unique_ID < B5.Unique_ID
JOIN HISTORY_INVOICES B7 ON B7.CLIENT_CODE = B1.CLIENT_CODE
AND B7.Unique_ID < B6.Unique_ID
JOIN HISTORY_INVOICES B8 ON B8.CLIENT_CODE = B1.CLIENT_CODE
AND B8.Unique_ID < B7.Unique_ID
JOIN HISTORY_INVOICES B9 ON B9.CLIENT_CODE = B1.CLIENT_CODE
AND B9.Unique_ID < B8.Unique_ID
JOIN HISTORY_INVOICES B10 ON B10.CLIENT_CODE = B1.CLIENT_CODE
AND B10.Unique_ID < B9.Unique_ID
WHERE B1.Unique_ID < A.Unique_ID)

There may be differences in speed between these two queries, my guess
is that the latter is quicker with larger tables and duplicates for
client_code.

If both of these are too slow, write a stored procedure.
HTH,
Set

--- In firebird-support@yahoogroups.com, "fabianchocron" wrote:
> 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