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 12345»»»

ROW_NUMBER(): An Efficient Alternative to Subqueries Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 9:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:30 AM
Points: 229, Visits: 167
Comments posted to this topic are about the item ROW_NUMBER(): An Efficient Alternative to Subqueries
Post #714684
Posted Tuesday, May 12, 2009 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 9:14 AM
Points: 14, Visits: 71
Using non-deterministic functions like ROW_NUMBER and NEWID need to be done carefully - my blog article: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/06/12/bug-in-inline-expansion-of-non-deterministic-functions-in-derived-tables-and-cte-s-causes-incorrect-results.aspx explains the serious bug that exists in SQL Server 2005 with using them.

Tony.
Post #714753
Posted Tuesday, May 12, 2009 2:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, Visits: 534
And if you're using SQLServer2000, then you'll need to use a "TOP n" correlated subquery, since ROW_NUMBER() is not available.. eg:


select RecentVersion.*
from (select distinct ProductId from dbo.ProductVersion) ProductList
inner join dbo.ProductVersion RecentVersion
on RecentVersion.ProductId = ProductList.ProductId
and cast(RecentVersion.Version as varchar(10)) + '.' + cast(RecentVersion.MinorVersion as varchar(10)) + '.' + cast(RecentVersion.ReleaseVersion as varchar(10)) = (
select top 1 cast(x.Version as varchar(10)) + '.' + cast(x.MinorVersion as varchar(10)) + '.' + cast(x.ReleaseVersion as varchar(10))
from dbo.ProductVersion x
where x.ProductId = RecentVersion.ProductId
order by x.Version desc, x.MinorVersion desc, x.ReleaseVersion desc
)

Post #714772
Posted Tuesday, May 12, 2009 4:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
Other uses of Row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx




Madhivanan

Failing to plan is Planning to fail
Post #714859
Posted Tuesday, May 12, 2009 6:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 28, 2012 4:41 AM
Points: 26, Visits: 118
Great Article - thank you
Post #714904
Posted Tuesday, May 12, 2009 7:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 10, 2011 8:18 AM
Points: 85, Visits: 49
Nice example!

Alternativly you could use a cursor to make it more readable and improve performance over a nested query. That said i dont like cursors and do prefer common table expressions!

SET ROWCOUNT 0
go

IF OBJECT_ID('tempdb..#ProductVersion') IS NULL
BEGIN
CREATE TABLE #ProductVersion
(
ProductID int NOT NULL,
Version int NOT NULL,
MinorVersion int NOT NULL,
ReleaseVersion int NOT NULL,
StandardCost numeric(30, 4) NOT NULL
)
END

DECLARE @ProductId INT
DECLARE A CURSOR FOR
SELECT ProductId
FROM ProductVersion
GROUP BY ProductId
ORDER BY ProductId
OPEN A
FETCH NEXT FROM A
INTO @ProductId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #ProductVersion
SELECT TOP 1 ProductId,
Version,
MinorVersion,
ReleaseVersion,
StandardCost
FROM ProductVersion
WHERE ProductId = @ProductId
ORDER BY ProductId ASC,
Version DESC,
MinorVersion DESC,
ReleaseVersion DESC

FETCH NEXT FROM A
INTO @ProductId
END
CLOSE A
DEALLOCATE A

SELECT * FROM #ProductVersion
DROP TABLE #ProductVersion

Post #715014
Posted Tuesday, May 12, 2009 8:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 7, 2014 11:51 AM
Points: 147, Visits: 220
Thanks for the great article - I thought that you did a good job of explaining how to use the row_number function and comparing its performance to subqueries.

Tim
Post #715126
Posted Tuesday, May 12, 2009 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 17, 2011 10:13 AM
Points: 3, Visits: 83
How could I leverage this to get, say, the "most current" version of a contract (i.e., either the contract currently in effect or the latest contract even if it was expired) *and* the latest version of the contract (i.e., it doesn't take effect until the future? I have a query using subqueries for this that looks something like this (this is simplified a bit). The row_number() query is much more concise but only returns that max contract date for each contract, which leaves out the current dates for those that have them:

SELECT DISTINCT group_number
,subgroup_number
,ISNULL(
(SELECT MAX(contract_thru_date)
FROM contracts curr1
WHERE curr1.group_number=curr.group_number
AND GETDATE() BETWEEN contract_from_date AND contract_thru_date
,(SELECT MAX(contract_thru_date)
FROM contracts prev
WHERE prev.group_number=curr.group_number
)
) contract_thru_date FROM contracts curr

EDIT: NM, I figured it out. All I had to was add an or statement to the where in the CTE. Great article, came at a good time!
Post #715205
Posted Tuesday, May 12, 2009 9:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 266, Visits: 2,613
Well done article.

Question: I hate to open a can of worms on a different topic, but I'm so curious as to why you specified the nolock hint for the row_number version of the query. Do you know of specific problems that require use of nolock for this type of implementation?

Thanks.
Post #715229
Posted Tuesday, May 12, 2009 9:43 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
tony rogerson (5/12/2009)
Using non-deterministic functions like ROW_NUMBER and NEWID need to be done carefully - my blog article: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/06/12/bug-in-inline-expansion-of-non-deterministic-functions-in-derived-tables-and-cte-s-causes-incorrect-results.aspx explains the serious bug that exists in SQL Server 2005 with using them.

Afraid that I do not understand this. AFAIK, CTEs are table expressions like non-indexed views. If you write them non-deterministically (which is what you do in your blog article), then they return non-deterministic results. And if you reference a non-deterministic table expression multiple times, you are not guaranteed to get the same results. That's straight ANSI SQL to the best of my knowledge and I see no bug in Transact-SQL demonstrated here.

And as far as ROW_NUMBER() it only returns non-deterministic results if the OVER clause parameters are non-deterministic. If you use a deterministic ORDER BY (which also requires that the column set is uniquely orderable), then you will get deterministic results.

All this really demonstrates is that if you input non-deterministic expressions without instantiating them, then you will get non-deterministic results back. Garbage In, Garbage Out.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #715235
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse