CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
JM1
JM1

Reputation: 1715

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.

Upvotes: 3

Views: 265

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

You can use the With Ties clause in concert with Row_Number()

  SELECT Top 1 with Ties * 
  FROM #TEMP
  Order By Row_Number() over (Partition By StudentID Order by IncidentDate,IncidentID )

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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".

Upvotes: 6

Related Questions