getting a property associate with the result of an aggregate function

Question

I have a table used to store array elements, this elements belong to a frame, each element have a date and a comment (among other elements). Something like this:

MASTER_FRAME(K) ARRAY_ID(K) ARRAY_INDX(K)  DATE   COMMENT
              1            1             1 27-08-2010   'List 1 Newer'
              1            1             2 27-07-2010   'List 1 Older'
              1            2             1 27-08-2010   'List 2 Newer'
              1            2             2 27-07-2010   'List 2 Older'
...

the master_frame, the array_id and the indx are the primary keys of the table. I need to get a list of the arrays of a given frame, each with the index and comment of the element containing the latest date. So for the example, if I query for the master_frame = 1 I should get:

ARRAY_ID DATE  COMMENT
       1 27-08-2010 'List 1 Newer'
       2 27-08-2010 'List 2 Newer'

So far I can get most of what I want with the query:

SELECT array_id, MAX(date)
FROM my_table
WHERE master_frame = 1
GROUP BY master_frame, array_id

However retrieving the comment (or the array_indx) has proved to be more difficult than what I thought. Is it possible at all? if it is possible, how?

Answer 1

Assuming SQL Server 2005+

;WITH cte As
(
SELECT array_id, [date], comment,
ROW_NUMBER() OVER (PARTITION BY array_id ORDER BY [date] DESC) AS RN
FROM my_table
WHERE master_frame = 1
)

SELECT array_id, [date], comment
FROM cte 
WHERE RN=1;