SQL 2005 for the SQL2K Developer Part 2

  • Muhammad Choirul Amri

    SSC Veteran

    Points: 207

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcAmri/sql2005forthesql2kdeveloperpart2.asp

  • Mike C

    SSC-Insane

    Points: 23224

    Don't forget RANK(), DENSE_RANK() and NTILE().  See http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp for more info.  Also discusses other reasons IDENTITY column and other options are not optimal solutions.

    Thanks.

  • Muhammad Choirul Amri

    SSC Veteran

    Points: 207

    Hi Mike C

    thanks for remind for such cool sequential ordering

  • omhoge

    SSCertifiable

    Points: 5416

    You can get the old sample databases no longer included in 2005 here:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en#Instructions

    hth

    Skål - jh

  • rlively-343684

    SSC Veteran

    Points: 298

    Couldn't this same query:

    UPDATE Products

    SET UnitPrice = 0.9 * UnitPrice

    WHERE ProductID IN

    (

    SELECT ProductID FROM(

    SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS Number, ProductID FROM Products) AS B

    WHERE Number < = 10

    )

    Be written in SQL2k (or SQL2005 for that matter) using TOP instead of ROW_NUMBER? This query even looks simpler and easier to understand:

    UPDATE Products

    SET UnitPrice = 0.9 * UnitPrice

    WHERE ProductID IN

    (

    SELECT TOP 10 ProductID FROM Products

    ORDER BY UnitPrice DESC

    )

  • Mike C

    SSC-Insane

    Points: 23224

    What's more difficult is something like this:

    UPDATE Products

    SET UnitPrice = 0.9 * UnitPrice

    WHERE ProductID IN

    (

    SELECT ProductID FROM(

    SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS Number, ProductID FROM Products) AS B

    WHERE Number % 10 = 0

    )

    Or

    UPDATE Products

    SET UnitPrice = 0.9 * UnitPrice

    WHERE ProductID IN

    (

    SELECT ProductID FROM(

    SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS Number, ProductID FROM Products) AS B

    WHERE (Number >= 10 AND Number < 20)

    OR (Number >= 50 AND Number < 60)

    )

  • rlively-343684

    SSC Veteran

    Points: 298

    Are you saying that in the case of "discount the price for the top 10 most expensive products" that using TOP would indeed be a better choice, but for more complicated requirements ROW_NUMBER provides a valuable alternative to complicated queries?

  • Mike C

    SSC-Insane

    Points: 23224

    Absolutely.  The example given for the Top 10 was too simple to benefit (unless of course you're trying to port something to/from a system that does not support the TOP keyword).  If you want to sample values from every 10th item, or need to grab the #s 11-20; or #s 31-40 (or combinations, etc.), ROW_NUMBER is much easier to use (and often considerably faster) than most alternatives.  The alternatives often include a lot of complex self-joins to "number" the rows to achieve this same effect.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply