Clever Geek Handbook

How do I return the entry with the earliest date?

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

I will 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 dataset, but only the first 5 were included for brevity.

Data:

Data Image

Desired results:

Image of desired results

Below is a sample code.

  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.

+3


source to share


2 answers


In SQL Server, you can do this with 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 the "simplest incident" as the "least significant incident".

+6


source


You can use With Ties clause in conjunction with Row_Number ()



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


source






More articles:


All Articles