Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 for the SQL2K Developer Part 2 Expand / Collapse
Author
Message
Posted Friday, April 14, 2006 6:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 11, 2014 5:30 AM
Points: 9, Visits: 25
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcAmri/sql2005forthesql2kdeveloperpart2.asp
Post #273439
Posted Wednesday, May 10, 2006 9:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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.

Post #279111
Posted Thursday, May 11, 2006 3:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 11, 2014 5:30 AM
Points: 9, Visits: 25
Hi Mike C

thanks for remind for such cool sequential ordering
Post #279156
Posted Thursday, May 11, 2006 7:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 6, 2012 9:12 AM
Points: 500, Visits: 183

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
Post #279223
Posted Friday, May 11, 2007 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:21 PM
Points: 40, Visits: 43
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
)
Post #365084
Posted Friday, May 11, 2007 6:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135

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)
)

Post #365373
Posted Monday, May 14, 2007 7:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 2:21 PM
Points: 40, Visits: 43
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?
Post #365603
Posted Monday, May 14, 2007 8:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 1:20 PM
Points: 1,276, Visits: 1,135
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.
Post #365645
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse