SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ROW_NUMBER(): An Efficient Alternative to Subqueries


ROW_NUMBER(): An Efficient Alternative to Subqueries

Author
Message
Francis Rodrigues-459442
Francis Rodrigues-459442
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 168
Comments posted to this topic are about the item ROW_NUMBER(): An Efficient Alternative to Subqueries
tony rogerson
tony rogerson
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 76
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.
paul_ramster
paul_ramster
Mr or Mrs. 500
Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)Mr or Mrs. 500 (510 reputation)

Group: General Forum Members
Points: 510 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
)

Madhivanan-208264
Madhivanan-208264
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 476
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
Jason Rogers-460518
Jason Rogers-460518
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 118
Great Article - thank you
jochemvdende
jochemvdende
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 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


greenantim
greenantim
SSC-Enthusiastic
SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)SSC-Enthusiastic (193 reputation)

Group: General Forum Members
Points: 193 Visits: 245
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
William Hutton-462896
William Hutton-462896
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 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! w00t
JJ B
JJ B
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 2860
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.
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35582 Visits: 9518
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."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search