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
 General SQL Server Forums
 New to SQL Server Programming
 Select Max vakue then return different column

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-----Balance
1---------213---------10
2---------214---------20
1---------215---------15
1---------216---------20
2---------217---------15

Initially I want to find the Max Transid for each account and then take the balance at that point.

So I would return
Accid--------Balance
1------------20
2------------15

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-15 : 07:13:44
[code]SELECT AccID, Balance
FROM (
SELECT AccID, RowNum = Row_Number() OVER (ORDER BY TransID DESC PARTITION BY Accid), Balance
FROM myTable) AS a
WHERE RowNum = 1[/code]

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

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 Balance
UNION ALL
SELECT 2 AS AccID,214 AS TransID,20 AS Balance
UNION ALL
SELECT 1 AS AccID,215 AS TransID,15 AS Balance
UNION ALL
SELECT 1 AS AccID,216 AS TransID,20 AS Balance
UNION ALL
SELECT 2 AS AccID,217 AS TransID,15 AS Balance
)

SELECT
AccID,
Balance
FROM
(
SELECT
row_number() over(partition by(AccID) order by TransID desc) as rownum,
AccID,
Balance
FROM
_cte
) sss
WHERE
rownum=1

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete
Go to Top of Page

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, BalCash
FROM (
SELECT AccountID, RowNum = Row_Number() OVER (ORDER BY TransId DESC PARTITION BY AccountID), BalCash
FROM dbo.Trans) AS a
WHERE RowNum = 1
Go to Top of Page

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 Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete
Go to Top of Page

Stan1978
Starting Member

31 Posts

Posted - 2011-09-15 : 10:28:02
Got it, thanks for your help. Greatly appreciated.
Go to Top of Page
   

- Advertisement -