finding latest record

priyabrata mohanty · New User Joined: 04 Mar 2005 Posts: 8 Location: bangalore

Hi,
i need solution to the following problem.
i have a table with the following data-
code status record_insert_date
----- -------- --------------------
1000 w 02-04-2004
2000 e 01-03-2005
1000 w 03-04-2003
1000 w 04-06-2005
1001 e 01-02-2002
----
----
given the code and status, how to find out the most recently inserted record? e.g. if code = 1000 and status=w the record fetched must be the one with record_insert_date=04-06-2005.

Thanks and Regards,
Priyabrata

poolarajesh · New User Joined: 22 Jan 2004 Posts: 4

Hi,

Use max function to get the latest date as shown below.

select (req fields)
from table name
where code = 1000
and status = 'w'
and record_insert_date = (select max (record_insert_date )
from table name
where code = 1000
and status = 'w')

Correct me if i am wrong.

Rajesh

nrsmca · New User Joined: 25 May 2005 Posts: 14

select (a.cols,?)
from tablename a
and a.record_insert_date = (select max (b.record_insert_date )
from tablename b
where b.code = 1000
and b.status = 'w')