Getting max date and time from a database

[mozgheib]

Hello,

I have a table that has several records.
Each record is made up of 4 fields.

field1 : samplename
field2 : result
field3 : entrydate (type date format dd/mm/yyyy)
field4 : entrytime (type varchar2 format 00:00)

I want to write a query to get me one record
having the four fields however I want the record
with the maximum date up to midinght last night
and the maximum time of that retrieved maximum date.

Here is an example :

Sample1 2000 10/10/2003 00:00
Sample2 2000 11/10/2003 00:00
Sample3 2000 11/10/2003 10:00
Sample4 1020 30/03/2004 01:00

Assume today is the 30th of March.

The query should return the third record.
Please take note I need it for Oracle 7.1.3 I think
row_numer() and partition do not work on version 7.1.3.

Thanks.

 

[gunjan14]

Hi,

There may be better query but this can also work

SQL> create table temp_try_gunjan
  2  (
  3      col1   number,
  4      col2   number,
  5      last_date   date,
  6      last_time   varchar2(6)
  7  );

select a.last_date, a.last_time
from   temp_try_gunjan a,
       (Select max(last_date) max_date
        from   temp_try_gunjan
        where  last_date < trunc(sysdate)
       ) b
where  a.last_date = b.max_date
and    to_date(a.last_time,'HH24:MI') 
       = (select max(to_date(c.last_time,'HH24:MI'))
          from   temp_try_gunjan c
          where  c.last_date = a.last_date
         )
;

Gunjan

 

[carp]

This should work (as long as there is only one record corresponding to any given date/time!):

SELECT * FROM my_table
WHERE to_date(column3||' '||column4,'MM/DD/YYYY HH24:MI') = 
       (SELECT max(to_date(column3||' '||column4,'MM/DD/YYYY HH24:MI'))
            FROM my_table 
           WHERE to_date(column3||' '||column4,'MM/DD/YYYY HH24:MI') < trunc(sysdate));

Elbert, CO
1031 MST