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 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find the 5 largest orders for each customer?

Execute the following Microsoft SQL Server T-SQL example script in Management Studio Query Editor to demonstrate listing of the top 5 orders for each customer.

-- SQL find top n records - T-SQL row number over partition by

USE AdventureWorks;

GO

DECLARE @n tinyint

SET @n = 5

SELECT   Customer,

         SalesOrderID,

         -- MSSQL format currency (money)

         DollarAmount = '$'+LEFT( convert(varchar,DollarAmount,1),

                                len(convert(varchar,DollarAmount,1))-3)

FROM     (SELECT Customer = s.Name,

                 soh.SalesOrderID,

                 DollarAmount = soh.TotalDue,

                 SequenceNumber = ROW_NUMBER()

                 OVER(PARTITION BY soh.CustomerID ORDER BY soh.TotalDue DESC)

          FROM   Sales.SalesOrderHeader AS soh

                 INNER JOIN Sales.Store s

                   ON soh.CustomerID = s.CustomerID) t

WHERE    t.SequenceNumber <= @n

ORDER BY Customer

GO

-- (2686 row(s) affected)

 

/* Partial results

 

Customer                SalesOrderID      DollarAmount

A Bike Store            45283             $37,643

A Bike Store            46042             $34,722

A Bike Store            44501             $26,128

A Bike Store            43860             $14,603

A Great Bicycle Company 44125             $3,450

A Great Bicycle Company 44793             $2,828

A Great Bicycle Company 45569             $2,828

A Great Bicycle Company 46378             $2,224

A Great Bicycle Company 49537             $622

A Typical Bike Shop     46343             $39,156

A Typical Bike Shop     44755             $37,725

A Typical Bike Shop     45536             $28,429

A Typical Bike Shop     44092             $5,440

*/

----------

-- SQL find top n records - RANK windowing function over partition by

-- RANK function may result in more than n records in a partition due to equal matches

USE AdventureWorks;

GO

DECLARE @n tinyint

SET @n = 5

SELECT   Customer,

         SalesOrderID,

         -- MSSQL format currency (money)

         DollarAmount = '$'+LEFT( convert(varchar,DollarAmount,1),

                                len(convert(varchar,DollarAmount,1))-3)

FROM     (SELECT Customer = s.Name,

                 soh.SalesOrderID,

                 DollarAmount = soh.TotalDue,

                 SequenceNumber = RANK()

                 OVER(PARTITION BY soh.CustomerID ORDER BY soh.TotalDue DESC)

          FROM   Sales.SalesOrderHeader AS soh

                 INNER JOIN Sales.Store s

                   ON soh.CustomerID = s.CustomerID) t

WHERE    t.SequenceNumber <= @n

ORDER BY Customer

GO

-- (2690 row(s) affected)

 

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