April 14, 2006 at 6:15 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcAmri/sql2005forthesql2kdeveloperpart2.asp
May 10, 2006 at 9:16 pm
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.
May 11, 2006 at 3:23 am
Hi Mike C
thanks for remind for such cool sequential ordering
May 11, 2006 at 7:13 am
You can get the old sample databases no longer included in 2005 here:
hth
Skål - jh
May 11, 2007 at 6:53 am
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
)
May 11, 2007 at 6:51 pm
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)
)
May 14, 2007 at 7:18 am
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?
May 14, 2007 at 8:44 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy