Issue with multiple records - 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)

Issue with multiple records

Author

Topic

Jdd9909
Starting Member

1 Post

Posted - 2007-10-22 : 17:39:28

Hello,

I'm trying to pull transaction information for a set of data that could have multiple prices, I want to choose the price that was created last.

So I have something like this:

Trans #----Trans Price-----Created Price Date
111---------75-------------Oct 13, 2007
123---------100------------Oct 1, 2007
123---------90-------------Oct 15, 2007

I would like transaction 123 to show up with the $90 price (latest date), in words:

IF Count(Trans #) > 1
Choose Price Where Date is greatest

Thanks!! This is driving me crazy!!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-22 : 17:42:54

Look at GROUP BY and choose the max([created price date]).

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-22 : 17:45:10

[code]
Declare @T Table ( Trans int, Price int, Date datetime)
Insert into @T
select 111, 75, 'Oct 13, 2007' union all
select 123, 100, 'Oct 1, 2007' union all
select 123, 90, 'Oct 15, 2007'

Select t.*
from @T T
Join ( Select Trans, Max(date) Mdate
From @T T2
Group by T2.Trans
) t3 On T.Trans = T3.trans And T.Date = T3. MDate

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 02:16:35


Select t.*
from @T T
where date= (Select Max(date) Mdate
		From @T T2
		where Trans=T.Trans
	)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 02:31:27

This will also get the records sorted by Trans.

-- Peso
SELECT	Trans,
	Price,
	Date
FROM	(
		SELECT	Trans,
			Price,
			Date,
			ROW_NUMBER() OVER (PARTITION BY Trans ORDER BY Date DESC) AS RecID
		FROM	@T
	) AS d
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2007-10-23 : 13:27:19

Good stuff for mee too.

Thanks Peso and Dinakar.

-- Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources