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.
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?
I have about 35 columns in this data set, but only included the first 5 below for brevity.
The sample code is below.
CREATE TABLE #TEMP (
INSERT INTO #TEMP (StudentID, SchoolID, StudentNameFull, IncidentID, IncidentDate)
(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
In SQL Server, you can do this using
select t.* from (select t.*, row_number() over (partition by StudentId order by IncidentDate, IncidentID) as seqnum from #TEMP t ) t where seqnum = 1;
This interprets "easiest incident" as "incidentId with the smallest value".