从行具有最大日期的表中选择信息 [英] 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屋！