Re: Select / Group by

[Bill Downall]

On Fri, 17 Aug 2001 17:06:37 +0100, Ben Petersen wrote:

>>From what I can see this is true,  but is not part of the specs 
>provided.  I'd be concerned to assume that I could rely on it.

Then your best bet is the view someone suggested, of 

CREATE VIEW lastdatetime (personid, lastDateTime) as +
select personid, max (datetime(transdate,transtime)) group by personid

But it will be slow as a snail with a large data set, since it cannot be 
indexed.  Better to add a computed column to your R:BASE table with 
the same formula, then index the computed column, and use it in your 
view.  

ALTER TABLE tablename +
  ADD COLUMN TranDateTime = +
  (DATETIME(TransDate, TransTime)) DATETIME

CREATE INDEX ON tablename TranDateTime

CREATE VIEW LastTranView +
(PersonID, LastTranTime) AS +
SELECT +
PersonID, +
MAX (TranDateTime) +
FROM tablename +
GROUP BY PersonID

SEL * FROM TableName t1, LastTranView v2 +
WHERE (t1.personid = v2.personid ) +
  AND (t1.TranDateTime = v2.LastTranTime)

Bill

Bill