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

*/

DROP TABLE Auction

DROP TABLE Product

DROP TABLE Bidder

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