Populating a field in a table using the max command from the same table

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 2000 Forums

Transact-SQL (2000)

Populating a field in a table using the max command from the same table

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-08 : 12:06:26

Ken writes "I have a table with customer transactions.


date/time                     trans$     UserId   LastTransAmount
2004-06-22 22:02:32.420        12.00        5          ?
2004-06-22 22:12:32.420        18.00        5          ?
2004-06-22 21:02:32.420        16.00        6          ?
2004-06-22 22:12:32.420        14.00        6          ?

I need to populate the lasttransamount with the last transaction amount for these users.
I tried using the group by into a temp table, but without grouping by the trans$, I could not get it to work correctly.

Here are the results i need:


date/time                     trans$     UserId   LastTransAmount
2004-06-22 22:02:32.420        12.00        5          18
2004-06-22 22:12:32.420        18.00        5          18
2004-06-22 21:02:32.420        16.00        6          14
2004-06-22 22:12:32.420        14.00        6          14

There are multiple dates in the file also.

Thanks,"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-08 : 13:22:08

Select
tranDate,
tranAmt,
UserId,
LastTransAmount = (Select max(tranAmt) From tranTable as B Where UserId = A.UserId)
From tranTable as A

Corey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-08 : 14:57:25

that value doesn't belong in a transaction table! why are you doing this?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources