Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1798
  • Last Modified:

Query to retreive records based on max(timestamp)

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
  • 2
1 Solution
Hi sunilbains,

perhaps something like

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

SharathData EngineerCommented:
you can also try this.
select b.id,b.name,b.class,b.update_tstp 
  from  t1 b
 where b.update_tstp = (select max(update_tstp) from t1 a where a.id = b.id)

Open in new window

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
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now