THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Relating unrelated rows

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2006/07/relating-unrelated-rows/

Published Friday, July 14, 2006 10:23 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

Hi Hugo,

Very interesting. You might be interested in my article on several similar problems:

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0401kuznetsov/index.html

Unfortunately, not all OLPA functions are implemented in SQL 2004 yet.
July 29, 2006 6:17 PM
 

Alexander Kuznetsov said:

Hi Hugo,

One more thing: if there are duplicates, RANK() has gaps and ROW_NUMBER() is better suited for the task. i think duplicates are quite possible in your situation. For instance, I bought by mail bags for my vacuum cleaner more than once from one and the same company.
July 30, 2006 5:16 PM
 

Hugo Kornelis said:

Hi Alexander,

You are absolutely right about using ROW_NUMBER rather than RANK if duplicates are possible. Thanks for the addition.

This also shows another bonus of the new ranking functions. The SQL Server 2000 version of my query would fail if duplicates were allowed in SalesOrderDetail and/or SalesOrderHeaderSalesReason, just like the SQL Server 2005 version with RANK(). But whereas the latter version is easily fixed with ROW_NUMBER, there's no easy fix for the SQL Server 2000 version.

Thanks for your comments (and for the link to your great article)!
August 12, 2006 3:00 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement