从SQL Server表中选择具有最大日期的不同行？

I need to get a set of distinct records for a table along with the max date across all the duplciates.

我需要为表格获取一组不同的记录以及所有duplciates的最大日期。

ex:

Select distinct a,b,c, Max(OrderDate) as maxDate
From ABC
Group By a,b,c

The issue is I get a record back for each different date.

问题是我得到了每个不同日期的记录。

Ex:

aaa, bbb, ccc, Jan 1 2009
aaa, bbb, ccc, Jan 28 2009

How can I limit this so I end up with only:

我怎样才能限制这一点,所以我最终只能:

aaa, bbb, ccc Jan 28 2009

I assume the issue is the gorup by and distinct not getting along well.

我认为问题是gorup并且明显不能相处得很好。

EDIT: Found the issue that was causing the problem, query results were as expected, not as above.

编辑:发现导致问题的问题,查询结果如预期,而不是如上所述。

3 个解决方案

#1

Something is wrong either with your query or with your example results, as what you describe shouldn't be possible. How about some actual SQL and actual results?

您的查询或示例结果有问题,因为您所描述的内容不应该是可能的。一些实际的SQL和实际结果怎么样?

In any event, you don't need distinct there since all you're selecting are your three grouped columns an an aggregate, so you'll by definition end up with all distinct rows. I've never tried this, so perhaps there is some misbehavior when using both of those. Have you tried removing the distinct? What caused you to put it there?

在任何情况下,您不需要在那里进行区分,因为您选择的是您的三个分组列和一个聚合,因此您将根据定义最终得到所有不同的行。我从来没有试过这个,所以也许在使用这两者时会有一些不当行为。你有没有尝试删除不同的?是什么原因让你把它放在那里?

#2

WITH q AS (
        SELECT  abc.*, ROW_NUMBER() OVER (PARTITION BY a, b, c ORDER BY orderDate DESC) AS rn
        FROM    abc
        )
SELECT  *
FROM    q
WHERE   rn = 1

Having an index on (a, b, c, orderDate) (in this order) will greatly improve this query.

在(a,b,c,orderDate)上建立索引(按此顺序)将大大改善此查询。

#3

If you run this query:

如果您运行此查询:

select 'ab' as Col1, 'bc' as col2, 'cd' as col3, getdate() as Date
into #temp
insert into #temp
values ('ab','bc','cd','1/15/09')
insert into #temp
values ('aa','bb','cc','1/1/09')
insert into #temp
values ('aa','bb','cc','1/22/09')

select col1,col2,col3,max(date)
from #temp
group by col1,col2,col3

You should get back:

你应该回来:

aa, bb, cc, 2009-01-22 00:00:00.000
ab, bc, cd, 2009-04-30 09:23:07.090

aa,bb,cc,2009-01-22 00:00:00.000 ab,bc,cd,2009-04-30 09:23:07.090

Your query will work as well so something is really wrong or you have not properly communicated the exact nature of your code.

您的查询也会起作用,因此出现问题或者您没有正确传达代码的确切性质。

#1