从表中选择信息，其中行具有最大日期 [英] Select info from table where row has max date

问题描述

我的桌子看起来像这样:

My table looks something like this:

group    date      cash  checks
  1    1/1/2013     0      0
  2    1/1/2013     0      800
  1    1/3/2013     0      700
  3    1/1/2013     0      600
  1    1/2/2013     0      400
  3    1/5/2013     0      200

-不需要现金就可以证明该表中有更多信息

-- Do not need cash just demonstrating that table has more information in it

我想获取日期最大且支票大于0的每个唯一组.因此返回结果如下:

I want to get the each unique group where date is max and checks is greater than 0. So the return would look something like:

group    date     checks
  2    1/1/2013    800
  1    1/3/2013    700
  3    1/5/2013    200

尝试的代码:

SELECT group,MAX(date),checks
    FROM table
    WHERE checks>0
    GROUP BY group
    ORDER BY group DESC

问题在于，它给了我所有的日期和支票，而不仅仅是最大日期行.

problem with that though is it gives me all the dates and checks rather than just the max date row.

使用ms sql server 2005

using ms sql server 2005

推荐答案

SELECT group,MAX(date) as max_date
FROM table
WHERE checks>0
GROUP BY group

这可以获取最大日期.将其重新加入数据以获取其他列:

That works to get the max date..join it back to your data to get the other columns:

Select group,max_date,checks
from table t
inner join 
(SELECT group,MAX(date) as max_date
FROM table
WHERE checks>0
GROUP BY group)a
on a.group = t.group and a.max_date = date

内部联接用作仅获取最大记录的过滤器.

Inner join functions as the filter to get the max record only.

仅供参考，您的列名很恐怖，请不要在列(组，日期，表)中使用保留字.

FYI, your column names are horrid, don't use reserved words for columns (group, date, table).

这篇关于从表中选择信息，其中行具有最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！