-1

I have a table with a lot of entries, and I'm stuck on how to get the records with the latest "CreatedDate"

This is my table:

enter image description here

I would like this to be the result:

enter image description here

Date: Shows which date the quantity belongs to.

CreatedDate: Show the latest "Status" update and therefore I only need to latest record based on Createddate.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Mikkel Tange
  • 1
  • 1

1 Answers1

1

You can use a window function like ROW_NUMBER() to generate a unique series of IDs ordered by the CreatedDate if you want to partition by a specific field or criteria, but if there's nothing to partition on and you just want all records that have the globally latest CreatedDate, you can do that simpler with a CTE like so:

WITH CTE_CreatedDate_Latest AS
(
    SELECT MAX(CreatedDate) AS CreatedDate
    FROM YourTable
)

SELECT Y.Id, Y.Date, Y.CreatedDate, Y.CreatedByMemberId, Y.Quantity, Y.MemberId, Y.Status
FROM YourTable AS Y
INNER JOIN CTE_CreatedDate_Latest AS C
     ON Y.CreatedDate = C.CreatedDate
J.D.
  • 40,776
  • 12
  • 62
  • 141