返回特定ID的最大日期值 [英] Return value at max date for a particular id

问题描述

这是我的sql服务器表

  ID日期值
 ___ ____ _____ 
 3241 9/17 / 12 5 
 3241 9/16/12 100 
 3241 9/15/12 20 
 4355 9/16/12 12 
 4355 9/15/12 132 
 4355 9/14/12 4 
 1234 9/16/12 45 
 2236 9/15/12 128 
 2236 9/14/12 323 
 2002 9/17 / 12 45 
  

这似乎应该很容易做，但我不知道为什么我'卡住了我想为每个ID选择最大（日期）和最大值（日期）的值。我想忽略对于每个ID不是最大（日期）的所有其他日期。

这是我希望表格如下所示： / p>

  ID日期值
 ___ ____ _____ 
 3241 9/17/12 5 
 4355 9 / 16/12 12 
 1234 9/16/12 45 
 2236 9/15/12 128 
 2002 9/17/12 45 
  / pre> 
 
 
我使用max（date）尝试组，但没有分组。我不知道我做错了什么感谢您的帮助！
解决方案
您可以使用以下内容：
  select t1.id，t2.mxdate，t1.value 
 from yourtable t1 
 inner join 
（
 select max ）mxdate，id 
 from yourtable 
 group by id 
）t2 
 on t1.id = t2.id 
 and t1.date = t2.mxdate 
  
 
 
 
请参阅 SQL Fiddle with Demo  
 
Here's my sql server table 
ID       Date       Value 
___      ____       _____
3241     9/17/12    5
3241     9/16/12    100
3241     9/15/12    20
4355     9/16/12    12
4355     9/15/12    132
4355     9/14/12    4
1234     9/16/12    45
2236     9/15/12    128
2236     9/14/12    323
2002     9/17/12    45
This seems like it should be easy to do, but I don't know why I'm stuck. I'd like to select ONLY the max(date) and value at that max(date) for each id. I want to ignore all other dates that aren't the max(date) with respect to each id.

Here's what I'd like the table to look like:
ID       Date       Value 
___      ____       _____
3241     9/17/12    5
4355     9/16/12    12
1234     9/16/12    45
2236     9/15/12    128
2002     9/17/12    45
I tried group by using max(date), but it didn't group anything. I'm not sure what I'm doing wrong. Thanks in advance for the help!
解决方案
You can use the following:
select t1.id, t2.mxdate, t1.value
from yourtable t1
inner join
(
  select max(date) mxdate, id
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.date = t2.mxdate


see SQL Fiddle with Demo

                        
这篇关于返回特定ID的最大日期值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！