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.
Author |
Topic |
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-09-25 : 03:56:47
|
How can I pull only latest product made by Cash on every first payment done?1 transaction have unique ID and can purchase few products in different time.I tried to pull as below but it still pull out Cash.ID Payment PaymentDate PaymentMethod123 $20 2012-01-10 Debit Card123 $50 2012-01-20 Cash234 $10 2012-01-30 Cash234 $80 2012-02-20 VisaI would like output as below:ID Payment PaymentDate PaymentMethod234 $10 2012-01-30 Cash |
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-09-25 : 04:07:39
|
select id,Payment, max(PaymentDate), PaymentMethod from table namewhere PaymentMethod ='Cash' group by select id,Payment,PaymentMethodSenthil Kumar C------------------------------------------------------MCITP - Database Administration SQL SERVER 2008MCTS - Database Development SQL SERVER 2008 |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-09-25 : 04:30:56
|
The output still there for ID 123ID Payment PaymentDate PaymentMethod123 $50 2012-01-20 Cash234 $10 2012-01-30 Cash |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-25 : 04:36:38
|
[code]select *from( select *, rn = row_number() over (order by PaymentDate desc) from yourtable where PaymentMethod = 'Cash') dwhere d.rn = 1[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
peace
Constraint Violating Yak Guru
420 Posts |
Posted - 2012-09-25 : 04:59:09
|
What I meant is I just want the output for every first transaction which use Cash.ID 123 first transaction is Debit Card according to the payment date.It should not appear in the output. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-09-25 : 05:55:49
|
[code]select *from( select *, rn = row_number() over (partition by ID order by PaymentDate desc) from yourtable where PaymentMethod = 'Cash') dwhere d.rn = 1and PaymentMethod = 'Cash'[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|