How to accomplish this query? - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

How to accomplish this query?

Author

Topic

Playerpawn
Starting Member

3 Posts

Posted - 2014-02-25 : 14:27:49

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

30421 Posts

Posted - 2014-02-25 : 15:48:16

[code]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;[/code]

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

Playerpawn
Starting Member

3 Posts

Posted - 2014-02-25 : 16:03:30

You should see my ridiculous ROW_NUMBER() solution. I don't know why that didn't come to me. Thank you, SwePeso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources