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