TSQL Lab I - Playing around with ROW_NUMBER

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


  • 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.

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

  • 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.


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

  • 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.

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


  • 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]

  • 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.

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

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