On 18 Aug 2001, at 9:03, Tom Grimshaw wrote:
> G'day Ben,
>
> (MAX(TransNum)) doesn't do it?
>
Nope.
Imagine the following:
personID TransNum, TransDate
100 1 1/10/2001
100 2 1/1/2001
200 3 3/1/2001
100 4 1/5/2001
Sel personID, (max(TransNum)),
(max(datetime(TransDate,TransTime))) from
TableNm group by MemberID
for ID 100 this returns 100, 4, 1/10/2001
when what I want is
100, _1_, 1/10/2001
Actually, I don't really need the date returned, just the id and
transnum.
Thanks,
Ben
> At 03:26 PM 17/08/2001 +0100, you wrote:
> >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
>
> Warmest regards,
>
>
> Tom Grimshaw
> coy: Just For You Software
> tel: 612 9552 3311
> fax: 612 9566 2164
> mobile: 0414 675 903
>
> post: PO Box 470 Glebe NSW 2037 Australia
> street: 3/66 Wentworth Park Rd Glebe NSW 2037
>
> email: [EMAIL PROTECTED]
> web: www.just4usoftware.com.au
>
> This email and any files transmitted with it are confidential to the
> intended recipient and may be privileged. If you have received this email
> inadvertently or you are not the intended recipient, you may not
> disseminate, distribute, copy or in any way rely on it. Further, you should
> notify the sender immediately and delete the email from your computer.
> Whilst we have taken precautions to alert us to the presence of computer
> viruses, we cannot guarantee that this email and any files transmitted with
> it are free from such viruses.
>
>
