JM1 JM1 -4 years ago 89
SQL Question

How do I return the record with the earliest date?

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.

Data:

Picture of data

Desired Results:

Picture of desired results

The sample code is below.

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


Thank you.

Answer Source

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;

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download