Select the latest record

[ridhirao22]

Greetings,

Using Oracle 10g. and need help to select the lastest record.
Data looks like this:
ID LastDate Quarter Product Status Rate PriorID
150256 17-Aug-10 20091 366 101 2.786 133342
133342 2-Nov-09 20091 366 101 3.04693 Null

I need to show the row with Lastdate as 2-Nov-09, ID being the primary key

Please advice and Thanks in advance,
RR

 

[Dagon]

Do you mean just the latest record on the table or the latest record within a particular grouping e.g. latest record for each product?

For Oracle-related work, contact me through Linked-In.

 

[Dagon]

And also, if there is more than one record with the same latest date, what do you want to do? Display all of them or just randomly pick one?

For Oracle-related work, contact me through Linked-In.

 

[ridhirao22]

Sorry, I got so confused and posted it. WHat I was doing is correct. Getting the lastest record on the product.

Thanks for you quick respones.

Sorry to bother!

 

[SantaMufasa]

Ridhirao,

I added a couple of more rows to your table (to create a different product ID for testing purposes):

select * from tt_RidHirao;

    ID LASTDATE           QUARTER    PRODUCT     STATUS       RATE    PRIORID
------ --------------- ---------- ---------- ---------- ---------- ----------
150256 17-AUG-10            20091        366        101      2.786     133342
133342 02-NOV-09            20091        366        101    3.04693
150257 22-JUL-10            20091        367        101      1.786     133343
133343 18-OCT-09            20091        367        101    2.04693

Here is code (and output) that, I believe, resolves your need:

select * from tt_RidHirao x
 where (LastDate,product) =
       (select max(LastDate),product
          from tt_RidHirao y
         where x.product = y.product
         group by product
       )
/

    ID LASTDATE           QUARTER    PRODUCT     STATUS       RATE    PRIORID
------ --------------- ---------- ---------- ---------- ---------- ----------
150256 17-AUG-10            20091        366        101      2.786     133342
150257 22-JUL-10            20091        367        101      1.786     133343

Let us know if this is satisfactory.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”