I have no idea why it doesn't work the same on two machines (I expect
both databases are dialect 3), but this particular problem is at least
possible to work around:

WHERE
EXISTS(SELECT * FROM caixa B
WHERE B.cli_cod = A.cli_cod AND A.cxa_cod = B.cxa_cod)
AND NOT EXISTS(SELECT * FROM caixa B1
JOIN caixa B2 on B1.cli_cod = B2.cli_cod
WHERE B1.cli_cod = A.cli_cod
AND B1.cxa_cod > A.cxa_cod
AND B2.cxa_cod > B1.cxa_cod)

(assuming that cxa_cod is unique for a particular cli_cod in caixa).

It will probably not be practical to do the same with select first 25,
here I'd rather try something like

AND EXISTS(SELECT * FROM clientes C2 WHERE A.cli_cod = C2.cli_cod
AND (SELECT COUNT(*) FROM clientes C3
WHERE c3.cli_nome < C2.cli_cod) < 25)

if required (though for performance it may be required to write a stored
procedure if you have lots of clientes).

My background is still Firebird 1.5, but I expect this to be valid for
Firebird 2.0 as well.

HTH,
Set

Fabio Gomes wrote:

> Hi, i have the following sql:
>
> SELECT A.cli_cod, A.cxa_cod, A.cxa_data_rec, A.cxa_valor_recebido,
> A.vnd_cod, C1.cli_nome, V.ved_nome
> FROM caixa A
> JOIN clientes C1 on (A.cli_cod=C1.cli_cod)
> JOIN vendedores V on (C1.ved_cod=V.ved_cod)
> WHERE
> A.cxa_cod IN
> (Select first 2 B.cxa_cod FROM caixa B WHERE B.cli_cod = A.cli_cod
> ORDER BY B.cxa_cod DESC)
> AND A.cli_cod IN
> (select first 25 cli_cod from clientes ORDER BY cli_nome ASC)
> AND cxa_data_rec IS NOT NULL
> Order by C1.cli_nome ASC, A.cxa_data_rec DESC
>
> I tested this query in my test server and it returns just 2 rows for
> each customer (clientes), but then i tried it in the other server, and
> it returns a lot of rows for each customer, it seens like it isn't
> respecting the first 2 on this select:
>
> (Select first 2 B.cxa_cod FROM caixa B WHERE B.cli_cod = A.cli_cod
> ORDER BY B.cxa_cod DESC)
>
> Both servers are running firebird 2, one is centos (the one the works)
> and the other one is Ubuntu (it doesn't work here)
>
> Anyone have any ideas why the Ubuntu server have this weird behavior?
>
>
> Thanx in advance for any replies,
>
>
> --
> Fabio Gomes