SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query to return the highest TransNo per CardNo
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cash2card
Starting Member

South Africa
2 Posts

Posted - 08/14/2013 :  03:13:06  Show Profile  Reply with Quote
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

Edited by - cash2card on 08/14/2013 03:14:04

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 08/14/2013 :  03:29:14  Show Profile  Reply with Quote
-- 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

Edited by - bandi on 08/14/2013 03:35:41
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

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

--
Chandu

Edited by - bandi on 08/14/2013 03:36:34
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30214 Posts

Posted - 08/14/2013 :  03:43:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

South Africa
2 Posts

Posted - 08/14/2013 :  03:51:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 08/14/2013 :  03:54:20  Show Profile  Reply with Quote
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

Sweden
30214 Posts

Posted - 08/14/2013 :  06:49:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 08/14/2013 :  08:03:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000