如何返回最早日期的记录? [英] How do I return the record with the earliest date?

问题描述

我需要为每个不同的学生 ID 返回第一条记录.在我的示例代码中，我有一个记录在同一日期有两个事件，另一个学生在不同日期有多个事件.

I need to return the first record for each distinct student ID. In my sample code, I have one record with two incidents on the same date, and another student with multiple incidents on different dates.

我需要选择最早的日期，如果同一日期发生了多个事件，则将最早的事件 ID 作为下一个条件.有什么好方法可以做到这一点?

I would need to select the earliest date, and if more than one happened on the same date, then the earliest incident ID as the next criteria. What's a good way to do this?

我在这个数据集中有大约 35 列，但为了简洁起见，只包含了下面的前 5 列.

I have about 35 columns in this data set, but only included the first 5 below for brevity.

数据:

预期结果:

示例代码如下.

  CREATE TABLE #TEMP (
  StudentID float,
  SchoolID float,
  StudentNameFull nvarchar(255),
  IncidentID float,
  IncidentDate date
  )

  INSERT INTO #TEMP (StudentID, SchoolID, StudentNameFull, IncidentID, IncidentDate)
  VALUES 
  (1111111, 406, 'Smith,John', 123321, '20170501'),
  (1111111, 406, 'Smith,John', 123322, '20170501'),
  (2222222, 406, 'Jones,Tim',  654789, '20170501'),
  (2222222, 406, 'Jones,Tim',  659872, '20170503'),
  (2222222, 406, 'Jones,Tim',  478978, '20170508')

  SELECT * FROM #TEMP

谢谢.

推荐答案

在 SQL Server 中，您可以使用 ROW_NUMBER():

In SQL Server, you can do this using ROW_NUMBER():

select t.*
from (select t.*,
             row_number() over (partition by StudentId order by IncidentDate, IncidentID) as seqnum 
      from #TEMP t
     ) t
where seqnum = 1;

这将最简单的事件"解释为具有最小值的事件 ID".

This interprets "easiest incident" as "incidentId with the smallest value".

这篇关于如何返回最早日期的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！