根据多个条件SQL选择，特定行？(SQL Select, Specific Rows based o

不知道是因为我累了，但我似乎无法想出解决办法...

我在寻找一个查询，将筛选基于几个项目中的数据...

样本数据：

Business_Month  ID  Calls   Transferred Loaded
11/1/2012 0:00  119118  226 16  12/19/12 15:56
12/1/2012 0:00  119118  333 17  1/15/13 23:54
1/1/2013 0:00   119118  284 6   3/13/13 17:49
1/1/2013 0:00   119118  284 6   3/20/13 13:03
1/1/2013 0:00   119118  284 6   3/20/13 13:25
2/1/2013 0:00   119118  219 8   3/20/13 13:25
3/1/2013 0:00   119118  17  0   3/20/13 13:03
3/1/2013 0:00   119118  17  0   3/20/13 13:25
11/1/2012 0:00  120327  216 13  12/19/12 15:56
12/1/2012 0:00  120327  211 12  1/15/13 23:54
1/1/2013 0:00   120327  255 8   3/13/13 17:49
1/1/2013 0:00   120327  255 8   3/20/13 13:03
1/1/2013 0:00   120327  255 8   3/20/13 13:25
2/1/2013 0:00   120327  166 9   3/20/13 13:25
3/1/2013 0:00   120327  13  2   3/20/13 13:03
3/1/2013 0:00   120327  13  2   3/20/13 13:25

我想回来，是一条线，每业务月份，具有最高加载时间每一个独特的ID ...

示例输出：

Business Month  ID  Calls   Transferred Loaded
11/1/2012 0:00  119118  226 16  12/19/12 15:56
12/1/2012 0:00  119118  333 17  1/15/13 23:54
1/1/2013 0:00   119118  284 6   3/20/13 13:25
2/1/2013 0:00   119118  219 8   3/20/13 13:25
3/1/2013 0:00   119118  17  0   3/20/13 13:25
11/1/2012 0:00  120327  216 13  12/19/12 15:56
12/1/2012 0:00  120327  211 12  1/15/13 23:54
1/1/2013 0:00   120327  255 8   3/20/13 13:25
2/1/2013 0:00   120327  166 9   3/20/13 13:25
3/1/2013 0:00   120327  13  2   3/20/13 13:25

我已经尝试不同的东西，但似乎无法推测出来......我使用的MS SQL Server ...

希望有人可以提供帮助。

谢谢！

Answer 1:

;WITH x AS 
(
  SELECT [Business_Month], ID, Calls, Transferred, Loaded,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY ID, [Business Month] ORDER BY Loaded DESC)
  FROM dbo.yourtable
)
SELECT [Business Month], ID, Calls, Transferred, Loaded
FROM x
WHERE rn = 1
ORDER BY ID, [Business Month];

Answer 2:

您可以使用子查询，以获得max(loaded)值对每个business_month ，然后加入该回yourtable以获得期望的结果：

select t1.Business_Month,
  t1.ID,
  t1.Calls,
  t1.Transferred,
  t1.Loaded
from yourtable t1
inner join
(
  select Business_Month, 
    max(Loaded) MaxLoaded
  from yourtable
  group by Business_Month
) t2
  on t1.Business_Month = t2.Business_Month
  and t1.Loaded = t2.MaxLoaded
order by t1.id, t1.business_month;

请参阅SQL拨弄演示