Hi Mike
>Yes Carl that one works but like my original one
Actually, it wasn't quite right - it needed a second join to item in the
subquery, so
select *
from hardone h1
where not exists (
select 1
from hardone h2
where h2.gmt_event > h1.gmt_event
and h2.item = h1.item)
(Thanks for that David. :-) Without that join, it will be fiendishly slow!
>
>SELECT * FROM hardone F
>WHERE F.GMT_EVENT =
>(SELECT MAX(W.GMT_EVENT)
> FROM hardone W
> WHERE W.ITEM = F.ITEM)
>
>it's doing a 'select' for every row that it looks at - which means for "n"
>rows in the hardone table it's going to do roughly n^2 operations.
Actually, no. "Not exists" is very efficient - it uses what's called a
"correlated subquery". The point is, the subquery is executed only once,
not executed for every record in the main query, so it's definitely of order
N, not N squared. I suggest you try it. :-)
>The kludge I used (see below database@delphi people) combines the two
>columns into 1 and gets the answer in one pass (order n). Speed is very
>important for this particular query. I just want to know if there is a
more
>formal way of doing it..
Understood. Usually I use "not exists" for this, and it gives good results.
Cheers,
Carl
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
