Best way to fetch last 4 rows from a result set using mysql

Question

Can any one please let me know, that, i need to fetch last 4 rows from a result-set using mysql. The result-set returns totally 6 records.

but, i need the records to be fetch from last4...i.e,

Record-3
Record-4
Record-5
Record-6

Answer 1

SELECT * FROM tablename ORDER BY id DESC LIMIT 0,4 

will give you the last 4 records ("last" when you order the table by id which is supposed to be an auto-increment field here.)

Answer 2

If you how that there is always 6 rows you could use limit.

SELECT * FROM Tabel LIMIT 2, 4

Answer 3

To get the last x number of rows, but have them returned in ascending order, use:

  SELECT x.value
    FROM (SELECT y.value
            FROM TABLE y
        ORDER BY y.value DESC
           LIMIT 4) x
ORDER BY x.value

The answer requires that you create a derived table (AKA inline view) based on the rows you want. Then the outer query re-orders the values for presentation.