Query to return single record from multiple like records

[tjm0713]

Not sure this can be done but here is my current situation. I have a table containing millions of records. Each record has 28 fields. One of the fields I am trying to return the single record for is Res_code. There can be multiple records with the same Res_code. Some of the matching Res_code records can have different values in any of the 28 fields and some may have exactly the same value in each field.

Example:
Res_Id Trans_Type Location Post_date
12345 N OKC 16-Jul-07
12345 N OKC 16-Jul-07
12345 Y OKC 16-Jul-07
12345 N OKC-AP 23-Jul-07

I have tryed a distict which will not work because I will receive 3 rows back. I have tried to use a partition with the same results. I have tried a sub query with the same results.
The only unique thing about these records is that the Post_date has a time stamp value, and each is different. I tried to do a max to_char using the time stamp but I still get back multiple rows.

Maybe I'm not seeing something that will work. I have asked our Oracle DBAs with no success.

Bottom line....I'm trying to perform a query which will only return me one row for each Res_id. And this row needs to be the most current row....this is where the post_date/time stamp come in.

If someone knows of a way to get these results I would appreciate any assistance.

[amitpatel66]

[code=oracle]

SELECT x.res_id, y.transtype,y.l ocation,x.pd FROM
(SELECT res_id,MAX(Post _date) pd FROM table1 GROUP BY res_id) x, table1 y
WHERE x.res_id = y.res_id
AND x.post_date = y.post_date

[/code]