Get the top rows by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get the top rows by date

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2008-12-11 : 09:50:35

Hi i need to get the top record for max date by Code

Example
Code Date Amt
A 2008-12-11 1000
A 2008-12-12 1100
A 2008-12-12 1100
A 2008-12-12 1100
B 2008-12-11 1100
B 2008-12-12 1100
B 2008-12-13 1100
B 2008-12-14 1300
A 2008-12-11 1100
C 2008-12-12 1100
C 2008-12-13 1100
C 2008-12-14 1200

Expected Result set will be

Code Date Amt
A 2008-12-12 1100
B 2008-12-14 1300
C 2008-12-14 1200

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-11 : 09:53:32

select Code,max(date)as maxdate,max(amt) as maxamt
from table
group by Code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 09:54:52

[code]SELECT Code,Date,Amt
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Date DESC) AS Seq,*
FROM TABLE)t
WHERE Seq=1[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-11 : 09:59:46

quote:
Originally posted by sodeep

select Code,max(date)as maxdate,max(amt) as maxamt
from table
group by Code

This would give wrong result. Try this,

select 
	a.code,a.maxdate,b.amt
from
	(select 
		Code,max([Date]) as maxdate 
	from 
		yourtable 
	group by
		Code
	)a
join 
yourtable  b on a.code=b.code and a.maxdate=b.[Date]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 10:02:13

quote:
Originally posted by sodeep

select Code,max(date)as maxdate,max(amt) as maxamt
from table
group by Code

the max values will not necessarily from same record which is not what OP wants. he wants details associated with recent record so it should be like this

select t1.*
from table t1
join
(
select Code,max(date)as maxdate
from table
group by Code
)t2
on t2.maxdate=t1.date
and t2.Code=t1.Code

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-12 : 00:25:53

quote:
Originally posted by sakets_2000

quote:
Originally posted by sodeep

select Code,max(date)as maxdate,max(amt) as maxamt
from table
group by Code

This would give wrong result. Try this,

select
a.code,a.maxdate,b.amt
from
(select
Code,max([Date]) as maxdate
from
yourtable
group by
Code
)a
join
yourtable b on a.code=b.code and a.maxdate=b.[Date]

Duplicate records are coming so use distinct keyword

select distinct t.code,t.date ,tp.amt
from
(select code, max(date) as date from #temp
group by code) t
left join
#temp tp on t.date = tp.date and t.code = tp.code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources