SQL 2005 for the SQL2K Developer Part 2
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcAmri/sql2005forthesql2kdeveloperpart2.asp
-
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.
-
Hi Mike C
thanks for remind for such cool sequential ordering
-
You can get the old sample databases no longer included in 2005 here:
hth
Skål - jh
-
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
)
-
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)
)
-
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?
-
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply