USE Northwind
GO

CREATE TABLE xCATEGORY (
		  CatCode int IDENTITY(1,1)
		, CatName varchar(50)
		, CatRank int
)

CREATE TABLE xCATITEM (
		  CatCode int 
		, ItemNum int
)

CREATE TABLE xITEMMAST (
		  ItemNum int IDENTITY(1,1) 
		, ItemDesc varchar(50)
)
GO

INSERT INTO xITEMMAST (ItemDesc)
SELECT 'SQL For Smarties' UNION ALL
SELECT 'ADMIN 911' UNION ALL
SELECT 'The Gurus...HTML' UNION ALL
SELECT 'Margaritas...Mans best freind' UNION ALL
SELECT 'The zen of Margarita mixing' UNION ALL
SELECT 'Tequila Sunrise...a margarita by any other name..'


INSERT INTO xCATEGORY (CatName, CatRank)
SELECT 'General',1 UNION ALL
SELECT 'COMP 101',2 UNION ALL
SELECT 'ADV COMP',3 UNION ALL
SELECT 'Lounge Lizards',1 UNION ALL
SELECT 'Bartending 101',2 UNION ALL
SELECT 'How not to drink your profits',3

INSERT INTO xCATITEM (CatCode, ItemNum)
SELECT 1,3 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,3 UNION ALL
SELECT 4,6 UNION ALL
SELECT 5,6 UNION ALL
SELECT 6,6
GO

SELECT CatRank, CatName, ItemDesc
  FROM XCATEGORY a
  JOIN xCATITEM  b ON a.CatCode = b.CatCode
  JOIN xITEMMAST c ON b.ItemNum = c.ItemNum 
  JOIN (SELECT ItemNum, MAX(CatRank) AS MAX_CatRank
	  FROM XCATEGORY d
	  JOIN xCATITEM  e ON d.CatCode = e.CatCode
	GROUP BY ItemNum) AS XXX ON XXX.ItemNum = b.ItemNum
				AND XXX.MAX_CatRank = a.CatRank
GO

DROP TABLE xITEMMAST
DROP TABLE xCATEGORY
DROP TABLE xCATITEM
GO

CatCode     CatName                                            CatRank     ItemNum     ItemDesc                                           
----------- -------------------------------------------------- ----------- ----------- -------------------------------------------------- 
3           ADV COMP                                           3           3           The Gurus...HTML
6           How not to drink your profits                      3           6           Tequila Sunrise...a margarita by any other name..