Get the row ID of the record which has the Max Date

Question

Hi I have a table with these columns

ID (increases by one each new row), Person, MeetingDate

I am selecting the last meeting for each person by using this query

SELECT Person, MAX(MeetingDate) FROM MyTable
GROUP BY Person

How could I get the ID for the last meeting for each person?

Thanks

A side note - while a GROUP BY clause often results in a resultset that "looks" ordered, that is an artifact of the execution plan. If order matters - and it usually does - your query needs an ORDER BY clause if you want a guaranteed order in the resultset. The situation you describe can be found by searching "first in group" — SMor, Oct 27 '21 at 12:53
Please add a [mcve] showing the exact table definition along with representative sample data. — Hannah Vernon, Oct 27 '21 at 15:35

J.D. · Answer 1 · 2021-10-27T16:19:40.493

You can use a window function such as the ranking function called ROW_NUMBER() to generate a numerical series within a grouping (PARTITION) of each Person ordered by the MeetingDate descending, like so:

SELECT ID, Person, MeetingDate
FROM
(
    SELECT
        ID,
        Person,
        MeetingDate,
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY MeetingDate DESC) AS SortId
    FROM MyTable
) AS Subquery
WHERE SortId = 1

You could also use a common table expression (CTE) instead of a subquery to neaten things up if you prefer:

WITH CTE_MyTable_Sorted AS
(
    SELECT
        ID,
        Person,
        MeetingDate,
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY MeetingDate DESC) AS SortId
    FROM MyTable
)
SELECT ID, Person, MeetingDate
FROM CTE_MyTable_Sorted
WHERE SortId = 1

The difference between using a subquery and a CTE is mostly just preference on organization / readability, but CTEs can be useful to keeping the code cleaner if you need to chain multiple together to do additional data manipulations (as opposed to multiple levels of subqueries).

Note, bbaird makes a very fair comment that if you have the case where the same Person has two different rows with the same exact MeetingDate then my previous queries above are non-deterministic and could return a different ID from within that same MeetingDate and Person grouping, each time the query is executed.

For example, if there are two rows in MyTable for Person A with MeetingDate 10/25/2021, one with ID 1 and the other with ID 2, then ordering on just the MeetingDate field is non-deterministic since it's the same MeetingDate for two different records for the same Person. In other words, which row comes first when the values being ordered on are exactly the same? In reality it's not possible to order one before the other, but in SQL Server, the answer is it's random.

To correct that, you would need to ensure you order by a unique expression. If your ID column is guaranteed to be UNIQUE then in the case where you have two rows with the same MeetingDate for the same Person, you could also add the ID to the end of the order by clause of the window function to ensure you always get deterministic results. If you want the largest (which generally is the latest - depending on how you're generating them) ID value when there are two rows for the same Person with the same MeetingDate then you would order by the ID field descending.

Here's an example of that using the CTE implementation from above:

WITH CTE_MyTable_Sorted AS
(
    SELECT
        ID,
        Person,
        MeetingDate,
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY MeetingDate DESC, ID DESC) AS SortId
    FROM MyTable
)
SELECT ID, Person, MeetingDate
FROM CTE_MyTable_Sorted
WHERE SortId = 1

Michael Bruesch · Answer 2 · 2021-10-27T12:34:23.130

3

You could get the MAX MeetingDate as you do now, then link that back to the original table to pull the ID like so:

SELECT ID
FROM MyTable AS T
INNER JOIN (
    SELECT Person, MAX(MeetingDate) AS MeetingDate
    FROM MyTable
    GROUP BY Person
) AS SUB
    ON T.Person = SUB.Person
    AND T.MeetingDate = SUB.MeetingDate

Or using a CTE like so:

WITH CTE_MAX_DATE AS (
    SELECT Person, MAX(MeetingDate) AS MeetingDate
    FROM MyTable
    GROUP BY Person
)
SELECT ID
FROM MyTable AS T
INNER JOIN CTE_MAX_DATE AS CTE
    ON T.Person = CTE.Person
    AND T.MeetingDate = CTE.MeetingDate

I personally love CTE's and use them whenever they are applicable.

edited Oct 27 '21 at 12:34

answered Oct 27 '21 at 12:16

Michael Bruesch

135
4

This will not be deterministic if there are multiple meetings on a given MeetingDate and we cannot make the assumption there is only one (especially given the hint of RowId) – Oct 27 '21 at 14:37
@bbaird You are correct if MeetingDate is a DATE instead of DATETIME but usually meetings happen at a specific time during the day. Assumptions were made. – Michael Bruesch Oct 27 '21 at 14:41
What if someone is double booked at a time? Nothing the author of the question suggests that a unique constraint exists that would hold that true. – Oct 27 '21 at 14:47

score 3 · Answer 3 · 2021-10-27T16:21:04.643

Since you mention "Row Id of the record" (not plural) I'm going to go out on a limb and say that a unique constraint may not exist and we would need to use some criteria to act as a "tie-breaker" to make the query deterministic - I'm working under the assumption that Id is set as the primary key¹ of the table, and using it as a tie-breaker².

Derived Table with Windowing Function

Absent any unique constraint, this is the only method that will:

Return one row person
Return the same row each time³:

You can use a CTE in place of the derived table (subquery), but that's a matter of preference.

SELECT
  Person
 ,MeetingDate
 ,Id
FROM
  (
    SELECT
      Person
     ,MeetingDate
     ,Id
     ,MeetingRank = ROW_NUMBER() OVER (PARTITION BY Person ORDER BY MeetingDate DESC, Id DESC)
    FROM
      MyTable
  ) LastMeeting
WHERE
  MeetingRank = 1

If there is a UNIQUE constraint on (Person, MeetingDate)

In this case you can use either use the above or use a correlated subquery. In most cases the windowing function will require a sort⁴ while the correlated subquery may be able to apply a TOP operator and avoid the sort.

SELECT
  Meeting.Person
 ,Meeting.MeetingDate
 ,Meeting.Id
FROM
  MyTable Meeting
WHERE
  Meeting.MeetingDate =
    (
      SELECT
        MAX(MeetingDate)
      FROM
        MyTable
      WHERE
        Person = Meeting.Person
          /* Can restrict date range by adding predicate on MeetingDate here */
    )

¹ This would function as a row pointer in the absence of another key (unique constraint).

² If Id isn't guaranteed to be unique through the primary key (or other appropriate UNIQUE constraint), you would need to sort by one or more columns to guarantee the row number assignment is deterministic.

³ Absent any inserts/updates/deletes on the table between when the query is run.

⁴ If the unique constraint is defined as (Person, MeetingDate DESC) no sort will be needed, but would be in other instances. Defining the constraint as such could be undesirable for other reasons.

This answer is no more correct than the other answers, as you make the assumption that OP wants the latest ID when there are multiples for the same MeetingDate. While your point about determinism is fair and important, so I'll update my answer accordingly regarding that, it stands to reason your answer is no more correct until we have further information from the OP. — J.D., Oct 27 '21 at 15:56
@J.D. Re-read my answer, I am clear about the ambiguity (unique/non-unique) and the appropriate solution depending on each. That's different than blindly assuming that there must be a unique constraint present. — , Oct 27 '21 at 16:07
I understand what your answer says, and I agree you make a fair point on determinism. But you also fell for the same assumption as me, since OP never stated ID is unique either, rather only that it's incremental. But we both know even an IDENTITY column specification doesn't guarantee uniqueness, only a UNIQUE CONSTRAINT does, which we have no knowledge on regarding OP's table currently. — J.D., Oct 27 '21 at 16:10
@J.D. Fair enough point RE: identity, but I'd say it's a safer assumption to make than others. I'll update my answer accordingly. — , Oct 27 '21 at 16:13
This was a very good thought-provoking discussion, much appreciated. You have my upvote. I've also updated my answer accordingly. — J.D., Oct 27 '21 at 16:16
@J.D. Thanks - helping people to understand how/why they would solve a problem one way or another hopefully is enlightening for readers and prevents them from learning the hard way on this or similar problems. — , Oct 27 '21 at 16:29

score 3 · Answer 4 · edited Oct 27 '21 at 21:37

3

SQL Server 2012 and later provide the function FIRST_VALUE that returns the first value in an ordered set of values. Use DISTINCT to return only one row for each partition (for each person). You can use ID as a tiebreaker.

SELECT DISTINCT
Person
,FIRST_VALUE(ID) OVER (PARTITION BY Person ORDER BY MeetingDate desc, ID desc)
FROM MyTable

edited Oct 27 '21 at 21:37

mustaccio

25,896
22
57
72

answered Oct 27 '21 at 20:36

emanuelbacigala

31
1

Might want to note FIRST_VALUE is only for versions 2012 and later. – Oct 27 '21 at 21:22

Get the row ID of the record which has the Max Date

4 Answers4