Query to retreive records based on max(timestamp)

Posted on 2009-04-28
Last Modified: 2013-12-07
Can anyone please advise on how to implement sql query for below logic-

There are columns- id,name,class and update_tstp for table T1.
I have to retreive all records based on max(update_tstp) for particular id column only,
but also, i have to show the data for corresponding name and class column for that particular id and update_tstp.
So in this case , I can't use following sql query--
select id,name,class,max(update_tstp)
from T1 group by id only and don't want group by name and class columns but want the data for name and class column to be shown for corressponding id and max(datestamp)
id name class update_tstp
3 Ram A 01/01/2009
3 Ramsteve E 02/02/2009
3 Ram A 03/02/2009
4 Cherry A 01/02/2009

I want as follows as a result from query--
id name class update_tstp
3 Ram A 03/02/2009 -->based on max(update_tstp) for id=3
4 Cherry A 01/02/2009
Question by:sunilbains
    LVL 25

    Expert Comment

    Hi sunilbains,

    perhaps something like

      from (select id, max(update_tstp) as update_tstp from t1 group by id) as v
      join t1 on = and t1.update_tstp = v.update_tstp

    LVL 40

    Accepted Solution

    you can also try this.
      from  t1 b
     where b.update_tstp = (select max(update_tstp) from t1 a where =

    Open in new window

    LVL 73

    Expert Comment

    must more efficient than double querying T1 to get the max is to use analytics...

    SELECT   id, name, class, update_tstp
      FROM   (SELECT   id,
                       ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_tstp DESC) rn
                FROM   t1)
     WHERE   rn = 1
    LVL 73

    Expert Comment

    I know this question is closed, but I high recommend using the analytic version over querying the table twice.

    Use autotrace or query your session statistics directly and you'll see should consume less resources particularly as t1 grows.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Note: You must have administrative privileges in order to create/edit Roles. ( (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now