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
 Query to return the highest TransNo per CardNo

Author  Topic 

cash2card
Starting Member

2 Posts

Posted - 2013-08-14 : 03:13:06
Hi All

Greetings from South Africa

I am very new too writing queries and would appreciate some help structuring a query to give me the CurrBal per CardNo.

I have table that looks like this:

CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-04 11:50 AM 79 0 6
2 2013-08-05 15:34 PM 52 100 41
2 2013-08-05 14:11 PM -48 0 40
3 2013-07-09 13:52 PM 49 0 12
3 2013-07-22 13:51 PM 11 0 14
1 2013-06-12 10:46 AM 63 0 7
3 2013-07-15 14:04 PM 33 0 13
2 2013-08-06 15:05 PM 39 0 42
2 2013-08-07 13:38 PM 30 0 43

I am looking to order this table by CardNo and then TransNo but i only want the query to display the record with the highest TransNo for each CardNo. In other words discard the records with the lower TransNo for each CardNo.

My desired result should hopefully look something like this:

CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-12 10:46 AM 63 0 7
2 2013-08-07 13:38 PM 30 0 43
3 2013-07-22 13:51 PM 11 0 14

I am using SQL 2012 Express but would also like this query to work in SQL 2005.

Please would you guys be so kind as to tell me how too write this query.

Thanks
Stephen

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 03:29:14
-- Query compatible with MSSQL 2005
SELECT T1.*
FROM TableName T1
JOIN (SELECT CardNo, MAX(TransNo) LatestTransNo FROM TableName GROUP BY CardNo ) T2
ON T1.CardNo = T2.CardNo AND T1.TransNo= T2.LatestTransNo

--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 03:34:05
-- Alternate is:
SELECT *
FROM TableName t1
WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )

--
Chandu
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-14 : 03:43:04
ROW_NUMBER() works in SQL Server 2005.
SELECT	CardNo,
DepDate,
CurrBal,
DepAmnt,
TransNo
FROM (
SELECT CardNo,
DepDate,
CurrBal,
DepAmnt,
TransNo,
ROW_NUMBER() OVER (PARTITION BY CardNo ORDER BY TransNo DESC) AS rn
FROM dbo.Table1
) AS d
WHERE rn = 1;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

cash2card
Starting Member

2 Posts

Posted - 2013-08-14 : 03:51:17
Thank You so Much
Worked like a charm and made my day.

quote:
Originally posted by bandi

-- Alternate is:
SELECT *
FROM TableName t1
WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )

--
Chandu

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 03:54:20
quote:
Originally posted by cash2card

Thank You so Much
Worked like a charm and made my day.

quote:
Originally posted by bandi

-- Alternate is:
SELECT *
FROM TableName t1
WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )

--
Chandu




Welcome

--
Chandu
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-14 : 06:49:19
I might be clueless in this case, but why is there a GROUP BY in the correlated subquery?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-14 : 08:03:30
quote:
Originally posted by SwePeso

I might be clueless in this case, but why is there a GROUP BY in the correlated subquery?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA


Good Catch SwePeso....
That was a typo
-- Alternate is:
SELECT *
FROM TableName t1
WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )


--
Chandu
Go to Top of Page
   

- Advertisement -