TSQL Lab I - Playing around with ROW_NUMBER

Question

TSQL Lab I - Playing around with ROW_NUMBER

jacob sebastian

SSChampion

Points: 11822
More actions
November 13, 2007 at 11:31 pm

#119470

Comments posted to this topic are about the item TSQL Lab I - Playing around with ROW_NUMBER
.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Nice article! One minor point is that I believe it is technically more correct in this case to use UNION ALL instead of UNION since there are no distinct values in the list you are combining together.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Attempted to run a modified sql1 (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c:\temp\items.xls', 'Select * from [items$]')

)

SELECT * FROM items

Ran using SQL 2000 Query Analyzer with the following results:

Server: Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'items$'. Make sure the object exists and that you spell its name and the path name correctly.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].

Any idea of what went wrong?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Kangana Beri SSCrazy Points: 2134 More actions · Answer 3

Bitbucket, the excel file needs to be on the same computer as the sql server. Check if thats the problem.

Also the CTE are new to Sql Server 2005, the WITH clause will not work in 2000.

jacob sebastian SSChampion Points: 11822 More actions · Answer 4

KB is right. The function ROW_NUMBER() itself is specific to SQL Server 2005. SQL Server 2000 does not support this.

.

Wayne West SSC-Insane Points: 22586 More actions · Answer 5

bitbucket (11/14/2007)
To those of you not old enough to remember - bitbucket a big iron term for logical unit 0

Now days the linux peoples are more likely to refer to it as /dev/null. 😛

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

bruce.cassidy Valued Member Points: 67 More actions · Answer 6

Nice example, and some good use of the WITH clause.

Some minor points: UNION ALL not UNION please (UNION incurs a sort). Also, I tend to use "left(Category + '0000', 4)" rather than "Category + replicate('0', 4-len(Category))" as it's a wee bit easier to code and read.

I've found the partition clause of ROW_NUMBER() extremely handy. I'm still experimenting though.