Ben,
It's too bad you didn't use a DATETIME datatype. then a single column
can be used to find the latest. With two columns, it's a lot more
complicated, since you want the latest date, but you only want the
latest time for rows where the date is equal to the latest time.
However, if your TransNum is a numerical sequence in chronological
order, it's easy:
CREATE VIEW LastTranByPerson +
(PersonID, TransNum) +
AS SELECT PersonID, MAX (TransNum) FROM tableName
Then you can either:
SELECT +
l1.PersonID, l1.TransNum, TransDate, TransTime, OtherStuff +
FROM LastTranByPerson l1, tableName t2 +
where (t2.transnum = l1.TransNum)
OR:
SELECT * FROM tableName WHERE transNum IN +
(SELECT Transnum from LastTranByPerson)
Bill
On Fri, 17 Aug 2001 15:26:00 +0100, Ben Petersen wrote:
>Hi all,
>
>I could use some help with a select statement.
>
>Imagine a table like this:
>
>personID, TransNum, TransDate, TransTime and other stuff
>
>This table may contain many entries for any personId, but only the
>one with the most recent TransDate/TransTime is valid. The data
>actually comes from a foreign DB and TransNum is the numerical
>sequence within the data file.
>
>I'd like a select statement that returns personID and TransNum for
>the most recent date/time for each personID for use in a view.
>
>At this point I have:
>
>Sel personID, (max(datetime(TransDate,TransTime))) from
>TableNm group by MemberID
>
>I haven't found a way to return TransNum from the same record that
>qualifies as (max(datetime....))
>
>TIA,
>
>Ben Petersen
>
