SQL: First Occuerence and with value

Question

我很少使用SQL，但是对于这个任务，它可能是最合适的。我希望创建一个查询，能够检测到每个主题的事件的第一次发生。

记录:

------------------------------
personID | date    | incident
------------------------------
1         20150901      F1
2         20150101      B2
3         20150301      C3
1         20150901      B2
3         20150401      R5
2         20150401      C3
1         20150701      F1

想要的结果:

------------------------------
personID | date    | incident
------------------------------
2         20150101      B2
3         20150301      C3
3         20150401      R5
2         20150401      C3
1         20150701      F1
1         20150901      B2

简单地说:我正在查找每个personID事件发生的第一次(基于日期)时间，忽略事件是否再次发生。

感谢

p。使用SQL Server 2008

Answer 1

使用MIN应该可以解决这个问题:

select personId,incident,MIN(convert(date,date)) as date
from [table]
group by personId,incident

Answer 2

试试这个:

;WITH
    temp AS (
        SELECT  personID, date, incident,
                rn = ROW_NUMBER() OVER (PARTITION BY personID, incident ORDER BY date)
        FROM    my_table
    )
SELECT  *
FROM    temp
WHERE   rn = 1

Answer 3

您可以使用row_number函数来获得所需的结果。

修改样本数据

select personid, date, incident
from
(
select *, row_number() over(partition by personid, incident order by date) as rn
from tablename
) x
where x.rn = 1;