Sorry about the error, Dany, it was due to me not naming the aggregate
column.

SELECT m2.MOVI, m2.PROC, m2.DSCR, m2.FECH, m2.HORA
FROM (select m.PROC, max(m.FECH) as FECH from MOVI m group by m.PROC) ms
JOIN MOVI m2 on ms.PROC = m2.proc and ms.FECH = m2.FECH

works on this computer and seems to be about twice as fast as the NOT
EXISTS, though there is something strange when I try the NOT EXISTS, so
there may be something not quite right with my copy of your database and
I'm not yet convinced which is the quicker solution.

Set

danyschaer wrote:

> Hi ALL;
>
> I have to thank all of you; both, SEAN LEYNE and HELEN ideas works very
> well. I didn't try SET's idea because yet (thank you Set !!!). I first
> didn't understand that, because I did an error when tried them.
>
> But now I see that the two are very good. I selected to use HELEN
> sintaxis for my project. The most important thing of both solutions is,
> that it is easy to combine them with filters in main table, without
> doing a first index in the related table (that can be very big), so in
> real cases the solutions you suggested me works very fine and quickly.
>
> For example:
>
> select "PROC"."PROC","PROC"."EXP1"
> as "EXPE","PROC"."ACTO","PROC"."DEMA","PROC"."OBSE","TPRO"."DSCR"
> as "D_TPRO", "mv1"."FECH", "mv1"."DSCR" from "PROC" left outer
> join "TPRO" on "PROC"."TPRO"="TPRO"."TPRO" left outer join "MOVI" "mv1"
> on "PROC"."PROC"="mv1"."PROC" where (("PROC"."PROC" in (select "PROC"
> from "PROC" where ("PROC"."INIC" between '20040101' and '20041231')))
> and ("mv1"."FECH"||"mv1"."HORA"=(select max("mv2"."FECH"||"mv2"."HORA")
> from "MOVI" "mv2" where "mv1"."PROC"="mv2"."PROC"))) order by lower
> ("PROC"."ACTO")
>
> returns me 2230 PROCs in just 3 or 4 seconds, what is very fast
>
> BTW, I don't use timestamps because a few things ...; but one is that I
> try to make my project to work with any database engine, and I don't
> know how far all the databases and the dbexpress drivers are
> compatibles in datatypes, so I always use strings for all the columns.
>
> I repead what I said once, this forum is amazing!!!.
>
> Thank you very very much.
>
> Dany