Select most recent distinct

Question

I am working on a report in SQL Server but cant seem to get it right. How can I return the a unique JNJobID's most recent JNNote joined to my Jobs table?

Select
    JobNotesID  
   ,JNJobID 
   ,JNDate      
   ,JNNote
From [JobNotes] Left JOIN Jobs ON [JobNotes].JNJobID = Jobs.JobID

Jobs Table

JobID   ProjectNum     
 6972   PN1
 6973   PN2
 6974   PN3

JobNotes Table

JobNotesID  JNJobID JNDate                       JNNote    
 11800      6972    2016-03-15 00:00:00.000     Example 1
 11874      6972    2016-03-17 00:00:00.000     Example 2
 12181      6972    2016-03-25 00:00:00.000     Example 3
 12006      6973    2016-03-21 00:00:00.000     Example 4
 11961      6974    2016-03-18 00:00:00.000     Example 5
 11924      6974    2016-03-17 00:00:00.000     Example 6

Would return

   JobID    ProjectNum          JNNote  
 6972        PN1               Example 3
 6973        PN2               Example 4
 6974        PN3               Example 6

This is a very frequent question and there are plenty of suitable duplicates tagged as [tag:greatest-n-per-group], but the one I choose as duplicate target has the solution and should suit you. — jpw, Mar 29 '16 at 17:46
Thank you. I am still looking for a solution on how to join the greatest-n-per-group results to another query based upon the JNJobID to link it to Jobs Table with JobID. — Daniel Maher, Mar 29 '16 at 18:03
That returned exactly what I wanted when all of the left joins were added. I should have mentioned earlier that the query is going into an MS Access report. Unfoturnately I dont believe partition by row_number is supported. I will reorganize my question and ask at a later date. — Daniel Maher, Mar 29 '16 at 19:28
SELECT customerID, CustomerName, AStreetAddress, ProjectNum, JobOwner, JobSubStatusID, Status, JNJobID, JNNote FROM ( SELECT customerID, CustomerName, AStreetAddress, ProjectNum, JobOwner, JobSubStatusID, Status, JNJobID, JNNote , ROW_NUMBER() OVER (PARTITION BY JNJobID ORDER BY JNDate DESC) AS r FROM JobNotes — Daniel Maher, Mar 29 '16 at 19:28
Left JOIN Jobs ON JobNotes.JNJobID = Jobs.JobID Left JOIN Addresses ON Jobs.JobAddressID = Addresses.AddressID Left JOIN Customers ON Jobs.JobCustomerID = Customers.CustomerID Left JOIN Status ON Jobs.JobSubStatusID = Status.StatusID ) x WHERE r = 1 and customerID = 134 and jobsubstatusid <> 14 and jobsubstatusid <> 15 and jobsubstatusid <> 16 and jobsubstatusid <> 42 and jobsubstatusid <>38 and jobsubstatusid <>75 — Daniel Maher, Mar 29 '16 at 19:28
No, row_number is not supported in Access. You would have to use the query with the max function instead, — jpw, Mar 29 '16 at 19:29

score -1 · Answer 1 · answered Mar 29 '16 at 17:41

You could use a the analytic version of max() to help recognize which row is the most recent for each ID. That requires a CTE or inline view:

select
  n.JNNote
from (
    select *, max(JNDate) over (partition by JNJobID) as maxDate
    from JobNotes
  ) n
where n.JNDate = n.maxDate

That does suppose that there are never two notes for the same job on the same date.

Select most recent distinct

1 Answers1