USE FoodDatabase
GO
/* Simple rank query */
SELECT Top 10 Display_Name, Grains,
    RANK() OVER (ORDER BY Grains DESC) as GrainRank 
    FROM Foods
    ORDER BY GrainRank
/* Same Query using WITH */
WITH Grain (Display_Name, Grains, GrainRank) AS 
(  
    SELECT Display_Name, Grains,
    RANK() OVER (ORDER BY Grains DESC) as GrainRank 
    FROM Foods
)
SELECT Top 10 Foods.Display_Name, Foods.Grains, GrainRank
 FROM Foods, Grain 
 WHERE Foods.Display_Name = Grain.Display_Name
 AND Foods.Grains = Grain.Grains
 ORDER BY Grains DESC
WITH Grain (Display_Name, Grains, GrainQuartile) AS 
(  
    SELECT Display_Name, Grains,
    NTILE(4) OVER (ORDER BY Grains DESC) as GrainQuartile 
    FROM Foods
)
SELECT Top 10 Foods.Display_Name, Foods.Grains,
 RANK() OVER (ORDER BY Foods.Grains DESC) as GrainRank 
 FROM Foods, Grain 
 WHERE Foods.Display_Name = Grain.Display_Name
 AND Foods.Grains = Grain.Grains
 AND Grain.GrainQuartile = 2
 ORDER BY Foods.Grains DESC
WITH Grain (Display_Name, Grains, GrainQuartile) AS 
(  
    SELECT Display_Name, Grains,
    NTILE(4) OVER (ORDER BY Grains DESC) as GrainQuartile 
    FROM Foods
),
Meat (Display_Name, Meats, MeatQuartile) AS
(  
    SELECT Display_Name, Meats,
    NTILE(4) OVER (ORDER BY Meats DESC) as MeatQuartile 
    FROM Foods
)
SELECT Top 10 Foods.Display_Name, Foods.Grains, Foods.Meats
 FROM Foods, Grain, Meat
 WHERE Foods.Display_Name = Grain.Display_Name
 AND Foods.Display_Name = Meat.Display_Name
 AND Grain.GrainQuartile = 1
 AND Meat.MeatQuartile = 1
 ORDER BY Foods.Meats DESC