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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 How to accomplish this query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Playerpawn
Starting Member

USA
3 Posts

Posted - 02/25/2014 :  14:27:49  Show Profile  Reply with Quote
Say you have source data much like:
NUMBER LETTER
11 C
10 C
10 B
9 B
8 B
7 A

... and you want one row for each letter, with the highest "number" not used in a higher letter.

Results:
11 C
9 B (not 10 B, because 10 was used in C)
7 A

Best way to achieve?

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 02/25/2014 :  15:48:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		NUMBER INT,
		LETTER CHAR(1)
	);

INSERT	@Sample
	(
		NUMBER,
		LETTER
	)
VALUES	(11, 'C'),
	(10, 'C'),
	(10, 'B'),
	(9, 'B'),
	(8, 'B'),
	(7, 'A');

SELECT		MAX(Number) AS Number,
		Letter
FROM		(
			SELECT		Number,
					MAX(Letter) AS Letter
			FROM		@Sample
			GROUP BY	Number
		) AS d
GROUP BY	Letter
ORDER BY	Letter DESC;



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

Playerpawn
Starting Member

USA
3 Posts

Posted - 02/25/2014 :  16:03:30  Show Profile  Reply with Quote
You should see my ridiculous ROW_NUMBER() solution. I don't know why that didn't come to me. Thank you, SwePeso!
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.03 seconds. Powered By: Snitz Forums 2000