DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find the nth highest price by color?

Execute the following T-SQL scripts in Microsoft SQL Server Management Studior Query Editor to find the nth highest price by color:

USE AdventureWorks;

-- Top product price in each color

SELECT Color,

       ListPrice

FROM   (SELECT Color,

               ListPrice,

               SequenceNo = ROW_NUMBER()

                              OVER(PARTITION BY Color ORDER BY ListPrice DESC)

        FROM   Production.Product

        WHERE  ListPrice > 0) a

WHERE  SequenceNo = 1

/*

Color ListPrice

NULL  229.49

Black 3374.99

Blue  2384.07

Grey  125.00

Multi 89.99

Red   3578.27

Silver      3399.99

Silver/Black      80.99

White 9.50

Yellow      2384.07

*/


-- Rank 6 price in each color

SELECT Color,

       ListPrice

FROM   (SELECT Color,

               ListPrice,

               RankNo = RANK()

                              OVER(PARTITION BY Color ORDER BY ListPrice DESC)

        FROM   Production.Product

        WHERE  ListPrice > 0) a

WHERE  RankNo = 6

/*

Color ListPrice

NULL  159.00

Red   2443.35

Red   2443.35

Red   2443.35

Red   2443.35

Silver/Black      40.49

Silver/Black      40.49

*/

 

-- DENSE RANK 2 price in each color

SELECT DISTINCT Color,

       ListPrice

FROM   (SELECT Color,

               ListPrice,

               RankNo = DENSE_RANK()

                              OVER(PARTITION BY Color ORDER BY ListPrice DESC)

        FROM   Production.Product

        WHERE  ListPrice > 0) a

WHERE  RankNo = 2

/*

Color ListPrice

NULL  196.92

Black 2443.35

Blue  1214.85

Multi 49.99

Red   2443.35

Silver      2319.99

Silver/Black      62.09

White 8.99

Yellow      1700.99

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE