Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2006
    Posts
    57

    Unanswered: Using Row_Number without stored procedure

    Is there a way to use Row_Number without using a stored procedure? All the examples on the internet are using stored procedure.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Sep 2006
    Posts
    57
    OK, I figured it out. But I have another question. My table is already ordered by date. Is there way I can avoid using the "Order By" clause in "Over"?

    I just want SQL to go to the specified row and get the next 10 rows after that. for example:

    Row Item Price
    100,000 Binoculars $99.99
    100,001 Computer $300
    100,002 Monitor $399


    Again, my table is clustered so there is no need to use the order by clause. In the table above if I needed to get only the rows specified 100,000 to 100,002 -- how would I go about this?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ...and it starts....worst thing M$ added as a feature

    Sure there is no requirement for you to use an order by...you can always rely on an arbitrary order
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2006
    Posts
    57
    Quote Originally Posted by Brett Kaiser
    ...and it starts....worst thing M$ added as a feature

    Sure there is no requirement for you to use an order by...you can always rely on an arbitrary order
    Could you give me an example? Everytime I take the OVER or ORDER BY clause out it gives me an error.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    ...and it starts....worst thing M$ added as a feature

    Sure there is no requirement for you to use an order by...you can always rely on an arbitrary order
    Worst thing? I think you give M$ too little credit for creativity. Surely they can do worse than that.
    But what is your beef with it. I admit I have not played around with it, but it would appear to be a good method of handling paging.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    My all time favorite comment in Microsoft code is in sp_who2
    Code:
          -- (Seems always auto sorted.)   order by spid_sort
    Sadly, this must have been tested only on single processor machines, or on machines that are not very busy. I get all sorts of orders from sp_who2.

  8. #8
    Join Date
    Sep 2006
    Posts
    57
    so is there a way to show only rows 50 to 60?

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if your table is "already ordered" because of a clustered index, there is no extra cost to adding an order by clause on that column, so just add it.

    The page tomh53 pointed you to has a very clear example of how to get rows 50-60. look at the second block of code on that page.

    I usually point people to this blog for paging though: http://weblogs.sqlteam.com/jeffs/arc...3/22/1085.aspx

  10. #10
    Join Date
    Feb 2005
    Posts
    33

    Paging

    Hi,

    Is there something similar to ROW_NUMBER() or rowid in SQL Server 2000 ?
    Or is it possible to sort in descending physical order (i mean the descending order of a "select * from table" command without "order by") to use the top/top technique without having primary/unique columns ?
    I would like to improve the paging functionality in a php based mssql query tool (that has to work with all tables - please don't advise me to set the primary key or add a unique column).

    Best regards
    ifx

  11. #11
    Join Date
    Sep 2006
    Posts
    57
    Quote Originally Posted by jezemine
    if your table is "already ordered" because of a clustered index, there is no extra cost to adding an order by clause on that column, so just add it.

    The page tomh53 pointed you to has a very clear example of how to get rows 50-60. look at the second block of code on that page.

    I usually point people to this blog for paging though: http://weblogs.sqlteam.com/jeffs/arc...3/22/1085.aspx
    I have tried to convert the stored procedure to regular sql commands and the commands are very fast except when I try to view page 13,000. When I try to view high numbered pages it gets really slow. Is there any way I can combat this? again I simulated 5 million rows in my table. Thanks for any replies

    also, here is the second block of code in that example:
    Code:
    returnpage 'select * from Orders','EmployeeID ASC, OrderDate DESC, OrderID ASC',12,31
    I entered this in my program but just exchanged the values but it keeps giving me an error

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •