How to get the last 15 records from a MySQL table in the right order

Posted on 2004-11-30
Last Modified: 2010-07-27
Dear Experts,

I have a MySQL table called "messages" which has two fields : name and message.

Now I need an example how to query this table and only fetch the LAST 15 records in (I guess) reverse order.

For example, when I have 1500 records:
The result must be one string containing record 1486, 1487, 1488 and so on: FIELD "name" + "some text" + field "MESSAGE" + " some text".

The idea is that people can enter a name and some short text in two fields on a webpage. Those fields are stored correctly into the table.
Now I need to display only the last 15 messages in chronological order, so last placed message last.

Kind regards,

Question by:MauriceW
    LVL 6

    Expert Comment

    using MS-ACCESS you can have a "LAST n"-statement in your SQL-statement.
    that is not possible with mysql.

    you will have to use a "counter" field wich value you can use in your statement ( eg. SELECT ... WHERE counter >= 1445 and counter < 1500) or fetch all records in an array and get the last 15 out of these
    LVL 18

    Accepted Solution

    I hope you either have a date field, or a primary key field, otherwise sorting will be impossible.

    Assuming you have an auto_increment primary key, you'd do:
        select name, message from messages order by message_id desc limit 15

    If instead you have a create_date field:
        select name, message from messages order by create_date desc limit 15

    These statements will select the last 15 records, but not in the order you want them. Since it's only 15, I suggest fetching them all, and reversing the result set in perl:

    my @messages;
    my $sth = $dbh->prepare('select name, message from messages order by message_id desc limit 15 ');
    while(my $r = $sth->fetchrow_hashref) {
        push @messages, $r ;

    foreach (reverse @messages) {
        print "Name: ", $_->{name}, $/;
        print "Message: ",  $_->{message}, $/;

    LVL 7

    Expert Comment

    AFAIK,  if no order clause is specified in a MySQL query, the order the rows  are retrieved is the order they were inserted, so if you need to fetch the last 15, try this:

    1. compute the total number of rows
      select count(*) from mesages;
    2. substract 15 to the count minus one (since first row is 0) and use that number in limit, so, for example, if the count is 100 try:

    select * from messages limit 84,15
    LVL 9

    Expert Comment

    Kandura gave you the right answer :) Listen to him


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
    In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now