sunilbains
asked on
Query to retreive records based on max(timestamp)
Hi,
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_t
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)
Ex:
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
Thanks
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_t
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)
Ex:
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
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
must more efficient than double querying T1 to get the max is to use analytics...
SELECT id, name, class, update_tstp
FROM (SELECT id,
name,
class,
update_tstp,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY update_tstp DESC) rn
FROM t1)
WHERE rn = 1
SELECT id, name, class, update_tstp
FROM (SELECT id,
name,
class,
update_tstp,
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.
Use autotrace or query your session statistics directly and you'll see should consume less resources particularly as t1 grows.
perhaps something like
select t1.id,t1.name,t1.class,t1.
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
lwadwell