View question - 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)

View question

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-23 : 08:36:19

I have a table


        [PaymentArrangementID] int IDENTITY(1, 1) NOT NULL,
	[DebtorID] int NULL,
	[FirstPaymentDate] datetime NULL,
	[LastPaymentDate] datetime NULL,
	[NextPaymentDate] datetime NULL,
	[NextReviewDate] datetime NULL,
	[DayOfMonth] tinyint NULL,
	[AmountInWords] varchar(60) NULL,
	[AmountInFigures] money NOT NULL,
	[PaymentTypeID] int NULL,
	[ArrangementDate] datetime NULL,
	[CompletionDate] datetime NULL,
	[NumberOfPayments] int NULL,
	[ExpiryDate] datetime NULL,
	[ArrangementComplete] bit NULL,
	[PaymentCategory] int NULL,
	[PaymentFrequency] int NULL,
	[CreatedBy] int NULL,
	[CreatedOn] datetime NULL,
	[ModifiedBy] int NULL,
	[ModifiedOn] datetime NULL,
	[Commission] bit NOT NULL,
	[TimeStamp] timestamp NULL

I want to return a list of all columns where each row is the maximum (latest) arrangementdate for each debtorid. How can I do this please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-23 : 08:43:02

select t1.* from your_table as t1 inner join
(
select debtorid,max(arrangementdate) as arrangementdate from your_table
group by debtorid
) as t2
on t1.debtorid=t2.debtorid and t1.arrangementdate =t2.arrangementdate

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-23 : 08:44:09

Do u mean this?

select * from table_name where arrangementdate=
(select max(arrangementdate) from table_name)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 08:45:15

select * from
(
select
row_number() over (partition by DebtorID order by ArrangementDate desc) as rownum,
*
from your_table
)dt
where rownum = 1

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 08:51:04

quote:
Originally posted by senthil_nagore

Do u mean this?

select * from table_name where arrangementdate=
(select max(arrangementdate) from table_name)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

This will not give values for each DebtorId

No, you're never too old to Yak'n'Roll if you're too young to die.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-10-23 : 09:12:02

Many thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources