SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast xiaohai's Avatar
    Join Date
    Apr 2003
    Location
    NE
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL newbie question: how do I select a row by row number?

    I'm new to Mysql and hope this question won't irritate too many people.
    I wanna select a row by its row number. I was wondering if there's any function that can do that. Now my table has a column call "id" which was set to autoincrement. "id" can distinguish each row but it can't represent the row number since when we delete one row, "id" won't decrement automatically. So do I need to add another collumn called "rownum" to specify the row number manually?
    Thanks.

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since you're using MySQL, you can do this:
    Code:
    SELECT *
    FROM your_table
    ORDER BY id DESC
    LIMIT row_num, 1
    where row_num is the no. of the row you want minus 1

  3. #3
    SitePoint Enthusiast xiaohai's Avatar
    Join Date
    Apr 2003
    Location
    NE
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot,man. That helps. Can you explain a little bit more about LIMIT as I can't really find information about how to use it.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    joel, are you assuming row_num is an actual column? variable? literal number pre-calculated before the query is run? i looked in the mysql docs and couldn't find anything about a row number

    xiaohai, may i ask why you need a row number? just curious

    rudy

  5. #5
    SitePoint Enthusiast xiaohai's Avatar
    Join Date
    Apr 2003
    Location
    NE
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy,
    I don't think Joel is assuming a new column. I've tried it and it worked just as I wanted without adding a new column. Maybe I'm going the long way, but what I'm trying to do is to display the whole table in a series pages instead of one. For example, I need to display a table of 100 rows in 10 pages. Then I need the row numbers to pull the rows out.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ah, paged output, now i get it

    just curious, but did you run it with row_num or with an actual number?

  7. #7
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    joel, are you assuming row_num is an actual column? variable? literal number pre-calculated before the query is run? i looked in the mysql docs and couldn't find anything about a row number

    xiaohai, may i ask why you need a row number? just curious

    rudy
    Nope row_num is a variable that's probably going to come from the programming language used. I am assuming though that "row number" means the position of the row, sorted by it's id (meaning smallest id is 1st row, next smallest is 2nd).

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thanks, it is as i had suspected -- by the time mysql sees it, it's a numeric literal

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't neet a row_number pseudo-column to accomplish this. The original auto_increment will do just fine, with deletes and all. LIMIT is an offset but is not calculated by taking start_id + limit_value. Try it, you'll be fine if you simply keep track of what ID you last ended at.

  10. #10
    SitePoint Enthusiast xiaohai's Avatar
    Join Date
    Apr 2003
    Location
    NE
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how do I delete rows using LIMIT? say, i wanna delete from row 5 to row 10 order by id DESC?


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
  •