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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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