sql获取具有最大日期和相对id记录的行

I try to make a simple query, spent on it a tone of hours and get nothing....
All I need is to get MAX date and all is corresponding fields.

我试着做一个简单的查询,花上一个小时,一无所获的语气....我只需要得到最大日期和相应的字段。

I'm explain: I have a table with this fields: BasketID, OrderStatusTypeID, StatusDate.
I try to get only one record that contain OrderStatusTypeID value with the last StatusDate.

我的解释是:我有一个表格，里面有:BasketID, ordershellstypeid, StatusDate。我尝试只获取一条记录，该记录包含最后一个StatusDate的ordercontrollstypeid值。

This is the data

这是数据

BasketID    OrderStatusTypeID   date
1111        13              2013-04-01 11:38:31
1111        26              2013-04-04 17:44:17
1111        39              2013-04-02 12:35:07
1111        40              2013-04-08 12:52:55

This is my query:

这是我的查询:

SELECT BasketID, OrderStatusTypeID, max(StatusDate) date
FROM st
where BasketID=1111
group by BasketID

This is the results i need

这就是我需要的结果

BasketID    OrderStatusTypeID   date
63558       40          2013-04-08 12:52:55

For some reason I only get OrderStatusTypeID = 13 and not 40! (max of StatusDate, and NOT max of OrderStatusTypeID).
Why???

因为某些原因，我只得到order降临id = 13而不是40!(StatusDate的max，而不是ordershellstypeid的max)。为什么? ? ?

BasketID    OrderStatusTypeID   date
63558       13                   2013-04-08 12:52:55

Thanks for fast response!

谢谢你的快速响应!

3 个解决方案

#1

I assume you are using MySQL because you can run the query even if you have not specified all the non-aggregate column in the GROUP BY clause.

我假设您正在使用MySQL，因为您可以运行查询，即使您没有指定GROUP BY子句中的所有非聚合列。

There are many ways to solve the problem but I'm used to do it this way. The query uses a subquery which separately gets the latest date for every BasketID. Since the subquery returned only two columns, you need to join it back on the table itself to get the other columns provided that it match on two columns: BasketID, Date.

有很多方法可以解决这个问题，但是我习惯了这样做。查询使用子查询，该子查询分别获取每个BasketID的最新日期。由于子查询只返回了两列，所以您需要将它连接回表本身，以获得其他列，前提是它匹配两个列:BasketID, Date。

SELECT  a.*
FROM    st a
        INNER JOIN
        (
            SELECT  BasketID, MAX(Date) max_date
            FROM    st
            GROUP   BY BasketID
        ) b ON  a.BasketID = b.BasketID AND
                a.Date = b.max_date

Your query has executed successfully without throwing an exception even though there are non-aggregate columns that are not specified in the GROUP BY clause because it is permitted in MySQL. See MySQL Extensions to GROUP BY.

查询已成功执行，没有抛出异常，即使GROUP BY子句中没有指定非聚合列，因为在MySQL中允许这样做。参见对组BY的MySQL扩展。

#2

I know this is an old thread, but why isn't it possible to use ORDER BY and LIMIT in this case? A query like that:

我知道这是一个旧的线程，但为什么不可能在这种情况下使用ORDER BY和LIMIT ?这样的查询:

SELECT * FROM st WHERE BasketID=1111 ORDER BY Date DESC LIMIT 1

选择*，其中BasketID=1111订单按日期DESC限制1

#3

Its pretty easy to use TOP command like:

使用TOP命令很容易，比如:

SELECT TOP 1 * FROM st 
WHERE BasketID = 1111
ORDER BY date DESC

#1