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 top 3 bidders in an auction?

Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Query Editor to demonstrate the use of the T-SQL PARTITION BY statement for locating top bidders in an auction.

-- SQL auction bidding - find top 3 bidders

-- Create and populate tables with SELECT INTO

USE tempdb;

SELECT   TOP ( 10 ) ProductID,

                    ProductName = Name,

                    Color,

                    ListPrice

INTO     Product

FROM     AdventureWorks.Production.Product

WHERE    ListPrice > 800.0

         AND Color IS NOT NULL

ORDER BY NEWID()

 

SELECT   TOP ( 30 ) BidderID = ContactID,

                    FullName = FirstName + ' ' + LastName

INTO     Bidder

FROM     AdventureWorks.Person.Contact

ORDER BY NEWID()

 

SELECT AuctionID = ROW_NUMBER() OVER (Order by (Select 1)),

      ProductID, BidderID,

      Bid = convert(money,0.8 * RAND(CHECKSUM(NEWID())) * ListPrice),

      BidDate= DATEADD(day,-10*RAND(CHECKSUM(NEWID())), getdate())

INTO Auction

FROM Product p

CROSS JOIN Bidder b

 

-- Populate for multiple bids from same bidder

INSERT Auction

SELECT AuctionID = ROW_NUMBER() OVER (Order by (Select 1)),

      ProductID, BidderID,

      Bid = convert(money,0.8 * RAND(CHECKSUM(NEWID())) * ListPrice),

      BidDate= DATEADD(day,-10*RAND(CHECKSUM(NEWID())), getdate())

FROM Product p

CROSS JOIN Bidder b

 

SELECT * FROM   Auction

/*

AuctionID  ProductID  BidderID      Bid         BidDate

1     969         12589       1505.8517  2009-11-21 12:04:54.337

2     969         3811        1155.5322  2009-11-25 12:04:54.337

3     969         1086        1248.0389  2009-11-18 12:04:54.337

*/

;

 

WITH CTE

     AS (SELECT Seq1 = row_number()

         OVER(PARTITION BY ProductID,BidderID ORDER BY Bid DESC),

                ProductID,

                BidderID,

                Bid,

                BidDate

         FROM   Auction),

     CTE1

     AS (SELECT Seq2 = ROW_NUMBER()

                         OVER(PARTITION BY ProductID ORDER BY Bid DESC),

                *

         FROM   CTE

         WHERE  Seq1 = 1)

SELECT   TopBidding = Seq2,

         ProductName,

         Bidder = FullName,

         Bid = '$' + CONVERT(VARCHAR,Bid,1),

         BidDate = CONVERT(DATE,BidDate)

FROM     CTE1 c

         INNER JOIN Product p

           ON c.ProductID = p.ProductID

         INNER JOIN Bidder b

           ON b.BidderID = c.BidderID

WHERE    Seq2 IN (1,2,3)

ORDER BY ProductName,

         TopBidding

GO

/* Partial results

TopBidding ProductName Bidder Bid BidDate
1 HL Mountain Frame - Black, 42 Cassidy Bennett $1,077.25 11/25/2009
2 HL Mountain Frame - Black, 42 Savannah Travers $1,031.60 11/18/2009
3 HL Mountain Frame - Black, 42 Aaron Nicholls $999.76 11/24/2009
1 HL Mountain Frame - Black, 44 Derrick Martin $1,066.78 11/25/2009
2 HL Mountain Frame - Black, 44 Janelle Fernandez $1,038.68 11/24/2009
3 HL Mountain Frame - Black, 44 Sharon Nath $1,032.19 11/22/2009
1 HL Mountain Frame - Silver, 48 Ruben Torres $1,054.39 11/23/2009
2 HL Mountain Frame - Silver, 48 Janelle Fernandez $931.76 11/26/2009
3 HL Mountain Frame - Silver, 48 Ian Hayes $901.90 11/23/2009

 

*/

DROP TABLE Auction

DROP TABLE Product

DROP TABLE Bidder

------------

 

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