2列不同的表并返回所有行

I want to get unique fields using distinct on 2 columns (ID and __EventType), however, I have been unsuccessful. Here is my attempt that failed but conceptualizes what I'm trying to achieve:

我想使用不同的2列（ID和__EventType）获取唯一字段，但是，我没有成功。这是我的尝试失败但概念化我想要实现的目标：

SELECT * FROM (select distinct ID, __EventType from SP_HS_Firm_Calendar_All);

For example here is All my data:

例如，这是我的所有数据：

Select ID, __DisplayName, __EventType,EventDate,EndDate from SP_HS_Firm_Calendar_All

Returns:

And here is the data I wish to return using distinct query above:

这里是我希望使用上面的不同查询返回的数据：

Thank you very much for all your help it is much appreciated!

非常感谢您的帮助，非常感谢！

5 个解决方案

#1

You can use row_number() in most databaes:

您可以在大多数数据库中使用row_number（）：

select t.*
from (select t.*, row_number() over (partition by id, _eventtype order by id) as seqnum
      from t
     ) t
where seqnum = 1;

#2

You can use GROUP BY

您可以使用GROUP BY

SELECT *
FROM SP_HS_Firm_Calendar_All
GROUP BY ID, __EventType;

#3

Please, try this one: Select DISTINCT (ID, __DisplayName) from SP_HS_Firm_Calendar_All

请尝试以下方法：从SP_HS_Firm_Calendar_All中选择DISTINCT（ID，__DisplayName）

#4

Have you tried just select distinct ID, __EventType from SP_HS_Firm_Calendar_All?

您是否尝试过从SP_HS_Firm_Calendar_All中选择不同的ID，__ EventType？

That alone theoretically should get you all rows with distinct combinations of ID AND __EventType.

从理论上讲，理论上应该使用ID和__EventType的不同组合来获取所有行。

#5

Here is the final solution, thank you very much!

这是最终的解决方案，非常感谢！

 select ID, __DisplayName, __EventType,EventDate,EndDate from (select t.*, row_number() over (partition by id, __EventType order by id) as seqnum from SP_HS_Firm_Calendar_All t) t where seqnum =1

#1