SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question MS SQL Server 2000/2003/2008

    Hi

    A simple yet weird question for MS SQL Server 2000/2003/2008 users.

    I have a table with 100 rows and there is NO auto-id number in that table. How do I fetch the x number of records from nth location?



    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    there is no "nth location"

    rows in a database table have ~no~ sequence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    ok lets make this this way


    What if I want to fetch rows from 20th record to 30th?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you cannot

    the rows aren't numbered
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    In mysql we do it with LIMIT 20,30 so there must be something similar to it in MS SQL?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    in neither mysql nor sql server does it make any sense to select the 21st through 51st row without an ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Sql 2005 or better can do this using ROWNUMBER. Before that, you are looking at lots of voodoo. If there are only 100 rows, I'd handle this stuff on the client side.

  8. #8
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Thanks for the help.

    Hw do we use ROWNUMBER in our query?



    Thanks

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cancer10 View Post
    Hw do we use ROWNUMBER in our query?
    that depends

    can we see your query please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thats my query

    Code:
    select firstname, lastname from employee

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what sequence would you like to show the employees in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Code:
    order by firstname

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT firstname
         , lastname 
         , ROW_NUMBER() 
             OVER ( ORDER BY firstname ) AS rn
      FROM employee
    ORDER 
        BY firstname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    736
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    But where did you mention that display only records from 20th to 30th?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT *
      FROM (
           SELECT firstname
                , lastname 
                , ROW_NUMBER() 
                    OVER ( ORDER BY firstname ) AS rn
             FROM employee
           ) AS d
     WHERE rn between 20 and 30
    ORDER 
        BY firstname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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