how to select the 5 latest row from my mysql

问题:

I wanted to know the sql command to retrieve 5 latest row from my table? Below is my sql query. How am I going to do, in order it can select the 5 latest row base on row no to be process?

$query = 
"SELECT lat, lng, DATE_FORMAT(datetime,'%W %M %D, %Y %T') AS 
  datetime FROM markers1 WHERE 1";

回答1:

You need to order the results, and set a limit.

Assuming datetime is what you wanted to order on:

$query = "
    SELECT 
        lat, 
        lng, 
        DATE_FORMAT(datetime,'%W %M %D, %Y %T') AS datetime 
    FROM markers1 WHERE 1
    ORDER BY datetime DESC
    LIMIT 5
";

---

EDIT: To answer OP's comment: "result that i get is start for Row 50 for first query and it follow by 49,48,47,46 Is that possible i can get this start frm row 46,47,48,49,50 ?"

You could either do this with the result in PHP, by fetching the rows and storing them in an array and reversing the array. I don't believe you can efficiently loop through a mysql result resource in reverse.

To do this in the SQL query, you need to create a temporary table with the original query:

$query = "
    SELECT 
        lat,
        lng,
        DATE_FORMAT(datetime,'%W %M %D, %Y %T') AS datetime 
    FROM (
        SELECT 
            lat, 
            lng, 
            datetime
        FROM markers1 WHERE 1
        ORDER BY datetime DESC
        LIMIT 5
    ) AS tmp_markers
    ORDER BY datetime ASC
";

The result of the initial query is used as the table to search in a new query, that orders by datetime ascending. I had to apply the DATE_FORMAT on the outer query because we need the datetime field to order by again.

回答2:

just add:

ORDER BY datetime DESC
LIMIT 5

回答3:

The best way to do this is to add a row number and then reverse your query based on that, since we don't have any guarantees that your datetime field increments chronologically; and I'm also assuming that by 5 latest rows you mean the last 5 added to the table, not the 5 with the most recent datetime.

SELECT
    lat
    , lng
    , DATE_FORMAT(datetime,'%W %M %D, %Y %T') AS datetime
    , @rownum:=@rownum+1 `RowNum`
FROM
    markers1
    , (SELECT @rownum:=0) `r`
WHERE 1
ORDER BY RowNum DESC
LIMIT 5

Check out this dude's blog for the rownumber solution I used.

回答4:

Might be a very late answer, but this is good and simple.

select * from table_name order id desc limit 5

This query will return a set of last 5 values(last 5 rows) you 've inserted in your table

回答5:

Check out my query for last 5 entry

SELECT * FROM notices where organizationid = $orgid ORDER BY `notices`.`id` DESC LIMIT 5

So, Most Important is ORDER BY notices.id DESC LIMIT 5