Author |
Topic |
cash2card
Starting Member
2 Posts |
Posted - 2013-08-14 : 03:13:06
|
Hi AllGreetings from South AfricaI 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 TransNo1 2013-06-04 11:50 AM 79 0 62 2013-08-05 15:34 PM 52 100 412 2013-08-05 14:11 PM -48 0 403 2013-07-09 13:52 PM 49 0 123 2013-07-22 13:51 PM 11 0 141 2013-06-12 10:46 AM 63 0 73 2013-07-15 14:04 PM 33 0 132 2013-08-06 15:05 PM 39 0 422 2013-08-07 13:38 PM 30 0 43I 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 TransNo1 2013-06-12 10:46 AM 63 0 72 2013-08-07 13:38 PM 30 0 433 2013-07-22 13:51 PM 11 0 14I 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.ThanksStephen |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 03:29:14
|
-- Query compatible with MSSQL 2005SELECT T1.*FROM TableName T1JOIN (SELECT CardNo, MAX(TransNo) LatestTransNo FROM TableName GROUP BY CardNo ) T2ON T1.CardNo = T2.CardNo AND T1.TransNo= T2.LatestTransNo--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 03:34:05
|
-- Alternate is:SELECT *FROM TableName t1WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )--Chandu |
|
|
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, TransNoFROM ( SELECT CardNo, DepDate, CurrBal, DepAmnt, TransNo, ROW_NUMBER() OVER (PARTITION BY CardNo ORDER BY TransNo DESC) AS rn FROM dbo.Table1 ) AS dWHERE rn = 1; Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
cash2card
Starting Member
2 Posts |
Posted - 2013-08-14 : 03:51:17
|
Thank You so MuchWorked like a charm and made my day.quote: Originally posted by bandi -- Alternate is:SELECT *FROM TableName t1WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )--Chandu
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-08-14 : 03:54:20
|
quote: Originally posted by cash2card Thank You so MuchWorked like a charm and made my day.quote: Originally posted by bandi -- Alternate is:SELECT *FROM TableName t1WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )--Chandu
Welcome--Chandu |
|
|
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 |
|
|
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 t1WHERE TransNo = (SELECT MAX(TransNo) FROM @tab WHERE CardNo= t1.CardNo GROUP BY CardNo )--Chandu |
|
|
|