从分组的MySQL数据中获取最新日期

I have the following data in my database:

我的数据库中有以下数据：

|NO | model | date     | 
+---+-------+----------+
|1  | bee   |2011-12-01|
|2  | bee   |2011-12-05|
|3  | bee   |2011-12-12|
|4  | tar   |2011-12-13|

I want to get the latest date of each model group:

我想获得每个模型组的最新日期：

| model | date     | 
+-------+----------+
| bee   |2011-12-12|
| tar   |2011-12-13|

I tried:

我试过了：

SELECT model, date 
FROM doc
WHERE date ........????? //what is the next?
GROUP BY model

8 个解决方案

#1

Are you looking for the max date for each model?

您在寻找每个型号的最大日期吗？

SELECT model, max(date) FROM doc
GROUP BY model

If you're looking for all models matching the max date of the entire table...

如果您正在寻找符合整个表格最大日期的所有型号......

SELECT model, date FROM doc
WHERE date IN (SELECT max(date) FROM doc)

#2

You can try using max() in subquery, something like this :

您可以尝试在子查询中使用max（），如下所示：

SELECT model, date  
FROM doc 
WHERE date in (SELECT MAX(date) from doc GROUP BY model);

#3

Subquery giving dates. We are not linking with the model. So below query solves the problem.

子查询给出日期。我们没有链接模型。所以下面的查询解决了这个问题。

If there are duplicate dates/model can be avoided by the following query.

如果有重复的日期/模型可以通过以下查询避免。

select t.model, t.date
from doc t
inner join (select model, max(date) as MaxDate from doc  group by model)
tm on t.model = tm.model and t.date = tm.MaxDate

#4

try this:

尝试这个：

SELECT model, date
FROM doc
WHERE date = (SELECT MAX(date)
FROM doc GROUP BY model LIMIT 0, 1)
GROUP BY model

#5

Using max(date) didn't solve my problem as there is no assurance that other columns will be from the same row as the max(date) is. Instead of that this one solved my problem and sorted group by in a correct order and values of other columns are from the same row as the max date is:

使用max（date）并没有解决我的问题，因为无法保证其他列与max（date）来自同一行。而不是这个解决了我的问题，并按正确的顺序排序组，其他列的值与最大日期的行相同：

SELECT model, date 
FROM (SELECT * FROM doc ORDER BY date DESC) as sortedTable
GROUP BY model

#6

-1

This should work:

这应该工作：

SELECT model, date FROM doc GROUP BY model ORDER BY date DESC

It just sort the dates from last to first and by grouping it only grabs the first one.

它只是将日期从最后一个排序到第一个，通过分组它只抓取第一个。

#7

-1

And why not to use this ?

为什么不使用这个呢？

SELECT model, date FROM doc ORDER BY date DESC LIMIT 1

#8

-3

SELECT model, date 
FROM doc
WHERE date = '2011-12-12'

you dont even need group by

你甚至不需要分组

If you are looking for max date, (ur example does not suggest this by the way)

如果您正在寻找最大日期，（您的示例不建议这样做）

SELECT model, MAX(date) 
FROM doc
WHERE date = '2011-12-12'
group by model

#1