SQLServerCentral / Discuss Content Posted by M. Choirul Amri / Article Discussions / Article Discussions by Author / SQL 2005 for the SQL2K Developer Part 2 / Latest Posts

RE: SQL 2005 for the SQL2K Developer Part 2

Mike C — Mon, 14 May 2007 08:44:00 GMT

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.

RE: SQL 2005 for the SQL2K Developer Part 2

rlively-343684 — Mon, 14 May 2007 07:18:00 GMT

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?

RE: SQL 2005 for the SQL2K Developer Part 2

Mike C — Fri, 11 May 2007 18:51:00 GMT

What's more difficult is something like this:

UPDATE ProductsSET UnitPrice = 0.9 * UnitPriceWHERE ProductID IN(SELECT ProductID FROM(SELECT ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS Number, ProductID FROM Products) AS BWHERE Number % 10 = 0)

RE: SQL 2005 for the SQL2K Developer Part 2

rlively-343684 — Fri, 11 May 2007 06:53:00 GMT

Couldn't this same query:UPDATE ProductsSET UnitPrice = 0.9 * UnitPriceWHERE 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 ProductsSET UnitPrice = 0.9 * UnitPriceWHERE ProductID IN( SELECT TOP 10 ProductID FROM Products ORDER BY UnitPrice DESC)

RE: SQL 2005 for the SQL2K Developer Part 2

omhoge — Thu, 11 May 2006 07:13:00 GMT

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

RE: SQL 2005 for the SQL2K Developer Part 2

Muhammad Choirul Amri — Thu, 11 May 2006 03:23:00 GMT

Hi Mike Cthanks for remind for such cool sequential ordering

RE: SQL 2005 for the SQL2K Developer Part 2

Mike C — Wed, 10 May 2006 21:16:00 GMT

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.

SQL 2005 for the SQL2K Developer Part 2

Muhammad Choirul Amri — Fri, 14 Apr 2006 18:15:00 GMT

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