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 |
Stan1978
Starting Member
31 Posts |
Posted - 2011-09-15 : 06:51:47
|
Thanks for reading and all past help, I think this is pretty simple but brain doesnt seem to be working!I have a table;AccID-----TransID-----Balance1---------213---------102---------214---------201---------215---------151---------216---------202---------217---------15Initially I want to find the Max Transid for each account and then take the balance at that point. So I would returnAccid--------Balance1------------202------------15 |
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-09-15 : 07:13:44
|
[code]SELECT AccID, BalanceFROM ( SELECT AccID, RowNum = Row_Number() OVER (ORDER BY TransID DESC PARTITION BY Accid), Balance FROM myTable) AS aWHERE RowNum = 1[/code]- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
|
|
Devart
Posting Yak Master
102 Posts |
Posted - 2011-09-15 : 07:23:12
|
Hi,For example:WITH _cte AS(SELECT 1 AS AccID,213 AS TransID,10 AS BalanceUNION ALLSELECT 2 AS AccID,214 AS TransID,20 AS BalanceUNION ALLSELECT 1 AS AccID,215 AS TransID,15 AS BalanceUNION ALLSELECT 1 AS AccID,216 AS TransID,20 AS BalanceUNION ALLSELECT 2 AS AccID,217 AS TransID,15 AS Balance)SELECT AccID, BalanceFROM(SELECT row_number() over(partition by(AccID) order by TransID desc) as rownum, AccID, BalanceFROM _cte) sssWHERE rownum=1Devart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete |
|
|
Stan1978
Starting Member
31 Posts |
Posted - 2011-09-15 : 08:09:40
|
Thanks guys - I have had a go with the first response - I dont think I can use the second as I have 100,000 accounts that I want to do this for.I get an error message (incorrect syntax near partition) when trying to run the following;SELECT AccountID, BalCashFROM ( SELECT AccountID, RowNum = Row_Number() OVER (ORDER BY TransId DESC PARTITION BY AccountID), BalCash FROM dbo.Trans) AS aWHERE RowNum = 1 |
|
|
Devart
Posting Yak Master
102 Posts |
Posted - 2011-09-15 : 08:35:47
|
Hi,RowNum = Row_Number() OVER (PARTITION BY AccountID ORDER BY TransId DESC)P.S.Both queries are equivalent ...Clause "WITH ...AS" had used as analogue of your table.Devart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete |
|
|
Stan1978
Starting Member
31 Posts |
Posted - 2011-09-15 : 10:28:02
|
Got it, thanks for your help. Greatly appreciated. |
|
|
|
|
|
|
|