Variant use of GROUP BY clause in TSQL

Question

Present the following schema and sample data (SQL Server 2008):

OriginatingObject
----------------------------------------------
ID
1
2
3

ValueSet
----------------------------------------------
ID   OriginatingObjectID   DateStamp
1    1                     2009-05-21 10:41:43
2    1                     2009-05-22 12:11:51
3    1                     2009-05-22 12:13:25
4    2                     2009-05-21 10:42:40
5    2                     2009-05-20 02:21:34
6    1                     2009-05-21 23:41:43
7    3                     2009-05-26 14:56:01

Value
----------------------------------------------
ID   ValueSetID   Value
1    1            28
etc (a set of rows for each related ValueSet)

I need to get the identifier of the most recent ValueSet for each OriginatingObject. Do not assume that the higher the record identifier, the later it is.

I'm not sure how to correctly use GROUP BY to make sure that the result set, grouped together to form each aggregate row, contains the row identifier with the highest DateStamp for this grouping. Do I need to use a subquery or is there a better way?

Answer 1

IN GROUP-BY.

: GROUP-BY . ValueSet, .

IN GROUP-BY (, ):

SELECT O.ID, V.ID
FROM Originating AS O, ValueSet AS V
WHERE O.ID = V.OriginatingID
AND
(V.OriginatingID, V.DateStamp) IN
(
    SELECT OriginatingID, Max(DateStamp)
    FROM ValueSet
    GROUP BY OriginatingID
)

:

SELECT O.ID, V.ID
FROM Originating AS O, ValueSet AS V
WHERE O.ID = V.OriginatingID
AND
V.DateStamp =
(
    SELECT Max(DateStamp)
    FROM ValueSet V2
    WHERE V2.OriginatingID = O.ID
)

Answer 2

SELECT OriginatingObjectID, id
FROM (
    SELECT id, OriginatingObjectID, RANK() OVER(PARTITION BY OriginatingObjectID 
                                    ORDER BY DateStamp DESC) as ranking
    FROM ValueSet)
WHERE ranking = 1;

Answer 3

This can be done using a correlated subquery. No need for GROUP-BY.

SELECT
  vs.ID,
  vs.OriginatingObjectID,
  vs.DateStamp,
  v.Value
FROM
  ValueSet vs
  INNER JOIN Value v ON v.ValueSetID = vs.ID 
WHERE
  NOT EXISTS (
    SELECT 1 
    FROM   ValueSet 
    WHERE  OriginatingObjectID = vs.OriginatingObjectID 
           AND DateStamp > vs.DateStamp
  )

This only works if there are no two identical DateStamps for the OriginatingObjectID in the ValueSet table.