SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Selecting rows by row number

    Is there any way to select a row by its row number. An auto_incremented field won't do because its consistency is broken after some rows are deleted.

    What I want to code is a kind of paged-results, but a bit more user-friendly.
    Lets consider we have a table of 26 rows with letters from 'a' to 'z' in a 'title' column. A common paging would look something like this: 1-10 11-20 21-26
    What I want to get is: a-j k-t u-z

    It would be nice to use one simple SQL-query like this:
    Code:
    SELECT RECNO(), title FROM table WHERE MOD(RECNO(),10) IN (0,9)
    But the problem is there is no anyting like RECNO() function in MySQL Well, at least, I haven't found one.

    Of course I can always use a loop of LIMIT-ed queries to get the titles I need one at a time (in fact, two at a time ). But in case of 1000-rows table it would require about 100 queries.

    Second solution I see is to add a 'row_number' column to the table, and renumerate it after deletes to keep it consistent. It seems a better solution because deletion (and addition) of rows would be rather rare.

    So, the question is if there is some MySQL-function (or some tricky hidden column?) that return the current row number. If not, what would be the best way to code such an alphabethic result paging?

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    you don't have to loop through various LIMITs, you may LIMIT the result set from any starting point:

    SELECT * FROM tbl LIMIT 45, 10

    45 is the offset (in fact it's the 46th row), and it would then return 10 rows from there
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    kleineme,
    What I need to get from the table are titles number 0, 9, 10, 19, 20, 29, 30, 39, etc. So I'll have to use a loop of SQL-queries. Something like this:
    PHP Code:
    for ($i 9$i NUMBER_OF_ROWS_IN_TABLE$i += 10) {
        
    mysql_query("SELECT title FROM table LIMIT $i, 2");
        ...


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    to ensure that you get reliable, consistent, and repeatable results, make sure you include an ORDER BY clause in that query
    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
  •