Subject [firebird-support] Re: group by ??
Author Svein Erling Tysvaer
Sorry about the error, Dany, it was due to me not naming the aggregate

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.


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 "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