如何从我的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，因为我们需要datetime字段来重新排序.

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屋！