Find the answer to your Linux question:
Results 1 to 10 of 10
how to select mysql db table's last records like if I have records 1 2 3 4 5 want to select last 2 records i.e 4,5...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Jan 2004
    Posts
    33

    mysql: viewing table last records


    how to select mysql db table's last records
    like if I have records
    1
    2
    3
    4
    5
    want to select last 2 records i.e 4,5

  2. #2
    Linux Guru sarumont's Avatar
    Join Date
    Apr 2003
    Location
    /dev/urandom
    Posts
    3,682
    Do you have the records numbered?

    Code:
    select * from TABLE where number_field>=count(TABLE)-1;
    Replace TABLE with the table name and number_field with the field that has the record numbers in them. I'm not sure exactly how to do this if you don't have a numbered field.
    "Time is an illusion. Lunchtime, doubly so."
    ~Douglas Adams, The Hitchhiker's Guide to the Galaxy

  3. #3
    Just Joined!
    Join Date
    Jan 2004
    Posts
    33
    perhaps it is not possible to grab records from bottom without some id

  4. #4
    Linux Guru
    Join Date
    Apr 2003
    Location
    London, UK
    Posts
    3,284
    Quote Originally Posted by sigix
    perhaps it is not possible to grab records from bottom without some id
    Correct.

  5. #5
    Linux Guru
    Join Date
    Oct 2001
    Location
    Täby, Sweden
    Posts
    7,578
    No, that's wrong - you can use the LIMIT condition in the SELECT clause. LIMIT fetches given number of rows from the start of the returned set, so obviously you'll need to reverse the sorting order. Something like this:
    Code:
    SELECT * FROM table ORDER BY id DESC LIMIT 2;

  6. #6
    Linux Guru sarumont's Avatar
    Join Date
    Apr 2003
    Location
    /dev/urandom
    Posts
    3,682
    But without an id column, you would have to find column to sort them by so as to get the ones you wanted on the bottom...depending on what you are using this for.
    "Time is an illusion. Lunchtime, doubly so."
    ~Douglas Adams, The Hitchhiker's Guide to the Galaxy

  7. #7
    Linux Guru
    Join Date
    Oct 2001
    Location
    Täby, Sweden
    Posts
    7,578
    Well, I guess I'm just assuming that everyone always sort their results. =)

    Well, that's not really it, though - I just thought that if you want the last results, you probably have an ordering anyway so that "last" has a defined meaning.

  8. #8
    Just Joined!
    Join Date
    Jul 2004
    Location
    Cambridge, MA
    Posts
    9
    Keep in mind that in MySQL the rows have to inherent order (although they may appear to, don't trust it!).

    So consider the real-world problem you want to solve. Do you want the last one inserted? This deals with time, and the best way to handle this is to have a timestamp on each record, sort the results by time DESCending, and LIMIT 1. You could also just get the max value for the column and then grab the record directly.

    I recommend you don't use ID to find the latest record, unless you handle the ID yourself (meaning NOT using autoincrement). The reason is that while the current behavior of autoincrement is MAX()+1, this behavior is NOT guaranteed to remain the same (in fact, the behavior changed not too long ago). What if future autoinc columns reuse deleted IDs?

    -Ian

  9. #9
    Just Joined!
    Join Date
    Feb 2009
    Location
    Colombia
    Posts
    2
    If you don't have an id or date column to sort by, (in my case the date column had incorrect values) you could try using LIMIT:


    Suppose we have a total 1000 rows in the table

    select count(*) from table;
    +----------+
    | count(*) |
    +----------+
    | 1000 |
    +----------+

    then,

    select * from table limit 950,1000

    would give us the last 50 rows

  10. #10
    Super Moderator devils casper's Avatar
    Join Date
    Jun 2006
    Location
    Chandigarh, India
    Posts
    24,729
    Hi and Welcome gigo6000 !

    Its a very old thread. I would suggest you to post in a bit recent threads.
    It is amazing what you can accomplish if you do not care who gets the credit.
    New Users: Read This First

Posting Permissions

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