3

I have the following query to get me the values corresponding with the latest date:

SELECT MAX(RowAddedDate), X, Y
FROM dbo.MyTable
GROUP BY X, Y

This is fine, but I need to get the ID of each row in this query. If I add the ID though, I get everything as the ID needs to be in the GROUP BY.

How do I solve this?

9

You can use window (ranking). functions for this:

WITH ct AS
  ( SELECT X, Y, RowAddedDate, ID,
           Rn = DENSE_RANK() OVER (PARTITION BY X, Y
                                   ORDER BY RowAddedDate DESC)
    FROM dbo.MyTable
  )
sELECT X, Y, RowAddedDate, ID
FROM ct 
WHERE Rn = 1 ;

If multiple rows have the same date with same X and Y, all of them will be returned due to the DENSE_RANK().

If you want only one, you can replace it with ROW_NUMBER() and adjust the ORDER BY to control which of the tied rows will be returned.

As for efficiency, an index on (X, Y, RowAddedDate) INCLUDE (ID) would help.

4

You could use a window MAX() to achieve the result in a way that is semantically very close to the one you have discovered but does not involve a join:

SELECT
  ID,
  X,
  Y,
  RowAddedDate
FROM
  (
    SELECT
      *,
      MaxRowAddedDate = MAX(RowAddedDate) OVER (PARTITION BY X, Y)
    FROM
      dbo.MyTable
  ) AS derived
WHERE
  RowAddedDate = MaxRowAddedDate
;

The maximum values of RowAddedDate per X, Y are returned alongside non-aggregated values. You just filter on the max values to get the rows you want.

1

OK, had a brain flash as soon as I posted this. Still, answer might help someone.

It is possible that the combination of MAX(RowAddedDate), X, Y in the query isn't unique, therefore the query can't be guaranteed to return a single ID per row.

As such, I will have to JOIN the table on itself with those three values as the join condition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.