如何从我的 mysql 中选择最新的 5 行 [英] how to select the 5 latest row from my mysql

问题描述

我想知道从我的表中检索 5 个最新行的 sql 命令?下面是我的 sql 查询.我该怎么办，以便它可以选择基于第 5 行 的最新行进行处理?

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";

推荐答案

需要对结果进行排序，并设置一个限制.

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
";

<小时>

回答 OP 的评论:我得到的结果是第 50 行的第一个查询开始，然后是 49,48,47,46 是否有可能我可以从第 46,47,48,49,50 行开始?"

---

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 ?"

您可以在 PHP 中使用结果执行此操作，方法是获取行并将它们存储在数组中并反转数组.我不相信您可以有效地反向循环遍历 mysql 结果资源.

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.

要在 SQL 查询中执行此操作，您需要使用原始查询创建一个临时表:

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
";

初始查询的结果用作在新查询中搜索的表，该查询按日期时间升序排列.我不得不在外部查询上应用 DATE_FORMAT，因为我们需要再次订购日期时间字段.

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.

这篇关于如何从我的 mysql 中选择最新的 5 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！