Re: How to retrieve latest record when date and time are separate ?

Walt <walt_at_boatnerd.com.invalid> wrote in message news:<3F0C5BA9.DB809458_at_boatnerd.com.invalid>...
> JN wrote:
> >
> > I record attendance of persons to table with these columns:
> > PERSON_ID, DATE, TIME, CODE .
> > Is it possible to write SQL query, which gives me latest record of all
> > persons? Unfortunately, the DATE and TIME are separate columns.
> > Thanks for any ideas.
>
> Yes, but you'll find life a lot simpler if you just use the built in
> date datatype. The best answer is to re-model your data design to take
> advantage of the functionality provided by Oracle.
>
> If you store date and time as strings and then try to manipulate them as
> dates you're re-enventing the wheel, working way to hard, and probably
> making some subtle mistakes along the way.

You can use below sql for your result

select * from test;

PERSON_ID DAT TIME CODE
--------- -------- ----- ----------

        1 26.04.98 07:00 First one
        2 08.07.03 16:00 Second
        2 09.07.03 13:00 Third
        2 09.07.03 11:00 Fourth

Select * from test
 Where to_Date(dat||Tim,'DD/MM/YYYY HH24:MI')

            =( Select Max(to_Date(dat||Tim,'DD/MM/YYYY HH24:MI') ) from test);

PERSON_ID DAT TIME CODE
--------- -------- ----- ----------

        2 09.07.03 13:00 Third Received on Thu Jul 10 2003 - 10:52:16 CEST