How to get the latest (or earliest) records?

[kuul13]

Hi

I have a history table (in oracle) with following columns..
split_id | policy | mod_dt | chng_cd
to maintain any changes to the policies

and I have a requirement to get all the latest changes for each policies. Please suggest an easy method to get the latest records for each policlies without using stored procedure or pl/sql.
The split_id field here is not unique. For eg:
split_id | policy | mod_dt | chng_cd
111111 |ABC11 |6/9/2006 11:40:16 AM | U
111111 |ABC11 |6/9/2006 11:40:16 AM | D
111112 |ABC12 |4/9/2002 01:12:11 AM | A
111112 |ABC12 |2/7/2005 07:10:31 AM | U
111112 |ABC12 |1/4/2007 10:22:19 AM | E

Please help!

[pclement]

How do you expect to retrieve the data from an Oracle table w/o using PL/SQL?

[kuul13]

Thanks for the reply Paul, am I expecting too much here? Can't I have an SQL statement to do this in Oracle 8i+ instead of PL/SQL (I mean procedure/function)? I am not sure but can I use RANK() and PARTITION?

[pclement]

Maybe I'm missing something here but RANK is a PL/SQL function. Are you using a tool or utility to retrieve this information or is there a programming language involved?

[kuul13]

May be I am not able to use the right terminology here, so I would like to reframe my problem... Actually I was reluctant to create an oracle stored procedure to do this task so I thought if someone can help me in writting a simple SQL query or subquery to accomplish this. Contrary to this, if you have a better and simple solution then please let me know. I am not using any other language/utility but need to get the data in SQLPlus/TOAD. Sorry for the confusion..

[pclement]

Off the top of my head see if the following works for you:

SELECT HT1.SPLIT_ID, HT1.POLICY, HT1.MOD_DT, HT1.CHNG_CD FROM HISTORY_TABLE HT1,
	(SELECT SPLIT_ID, POLICY, MAX(MOD_DT) AS MOD_DT, CHNG_CD FROM HISTORY_TABLE
		GROUP BY POLICY) HT2
	WHERE HT1.SPLIT_ID = HT2.SPLIT_ID AND
	      HT1.POLICY = HT2.POLICY AND
	      HT1.MOD_DT = HT2.MOD_DT AND
	      HT1.CHNG_CD = HT2.CHNG_CD
	ORDER BY SPLIT_ID

[charm]

Hi ,
try this one

select split_id , policy, max(mod_dt)
from history_table
group by split_id , policy

this will retrun like
111111 ABC11 2006-06-09 11:40:16.000
111112 ABC12 2007-01-04 10:22:19.000

Thanks
DG