SQL：日期两个单独行之间的差异

I have a table with the following format:

我有一个表格,格式如下:

id  |  Date A
----------------
1   |  05/02/12
1   |  05/05/12
1   |  05/08/12
3   |  05/01/12
3   |  05/05/12
3   |  05/06/12

Now I'd like to grab the latest (max) date for each id and group by id. So my SQL output should be:

现在我想按id获取每个id和group的最新(最大)日期。所以我的SQL输出应该是:

id  |  Date A
----------------
1   |  05/08/12
3   |  05/06/12

The dates are not necessarily on order. Can someone give me a hint on how to do this?

日期不一定是订单。有人能给我一个如何做到这一点的暗示吗?

Thanks!

2 个解决方案

#1

select id, max([Date A]) as 'MaxDate'
from table
group by id

#2

I'd use Windowing Functions for this.

我会使用窗口函数。

SELECT DISTINCT id, MAX([Date A]) OVER (PARTITION BY id) AS 'Date A'
  FROM table

The nice thing about Windowing Functions is that you do not have to GROUP BY things you do not need in the function to get your output results. They are also really fast.

窗口函数的优点在于,您不需要在函数中不需要GROUP BY来获取输出结果。他们也很快。

Here is the BOL: http://msdn.microsoft.com/en-us/library/ms189461.aspx Here is some more info on Windowing Functions: http://en.wikipedia.org/wiki/Select_(SQL)#Window_function

这是BOL:http://msdn.microsoft.com/en-us/library/ms189461.aspx这里有一些关于窗口函数的更多信息:http://en.wikipedia.org/wiki/Select_(SQL)# Window_function

#1

select id, max([Date A]) as 'MaxDate'
from table
group by id

#2