从行中包含最大日期的表中选择信息

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

- 不需要现金只是证明该表中有更多信息

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:

我想得到每个唯一的组，其中日期是最大值，检查大于0.所以返回看起来像这样：

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

attempted code:

尝试代码：

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.

问题虽然它给了我所有的日期和检查，而不仅仅是最大日期行。

using ms sql server 2005

使用ms sql server 2005

4 个解决方案

#1

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).

仅供参考，您的列名称很可怕，不要对列（组，日期，表）使用保留字。

#2

You can use a window MAX() like this:

您可以使用这样的窗口MAX（）：

SELECT
  *, 
  max_date = MAX(date) OVER (PARTITION BY group)
FROM table

to get max dates per group alongside other data:

获取每组最大日期以及其他数据：

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

Using the above output as a derived table, you can then get only rows where date matches max_date:

使用上面的输出作为派生表，您只能获得日期与max_date匹配的行：

SELECT
  group,
  date,
  checks
FROM (
  SELECT
    *, 
    max_date = MAX(date) OVER (PARTITION BY group)
  FROM table
) AS s
WHERE date = max_date
;

to get the desired result.

获得理想的结果。

Basically, this is similar to @Twelfth's suggestion but avoids a join and may thus be more efficient.

基本上，这与@ Twelfth的建议相似，但避免了连接，因此可能更有效。

You can try the method at SQL Fiddle.

您可以在SQL Fiddle上尝试该方法。

#3

you can use join like this. If you use IN this will perform slow try to avoid it.

你可以像这样使用join。如果您使用IN，这将执行缓慢尝试以避免它。

    SELECT *  FROM (SELECT  msisdn, callid, Change_color, play_file_name, date_played FROM insert_log
   WHERE play_file_name NOT IN('Prompt1','Conclusion_Prompt_1','silent')
 ORDER BY callid ASC) t1 JOIN (SELECT MAX(date_played) AS date_played FROM insert_log GROUP BY callid) t2 ON t1.date_played=t2.date_played

#4

SELECT group, date, checks
  FROM table 
  WHERE checks > 0
  GROUP BY group HAVING date = max(date)

should work.

应该管用。

#1

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