1

I have a table called DynamicText with the following fields: DynamicID, Content, Timestamp, and DynamicTextEnum.

I'm trying to do design a query that would select the most recent record based on Timestamp from each of the groups that are grouped by DynamicTextEnum.

For example:

Enum    Timestamp
-----------------
1       1/10/2012
1       2/10/2012
2       1/10/2012
3       3/10/2012
2       3/10/2012
3       4/10/2012

So the results would look like this:

Enum    Timestamp
-----------------
1       2/10/2012
2       3/10/2012
3       4/10/2012

My current simply SELECTS TOP 1 them based on Enum and orders them in DESC order based on Timestamp but it doesn't work when I need all Enums. Any ideas?

Justin Adkins
  • 1,214
  • 2
  • 23
  • 41

2 Answers2

2
;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DynamicTextEnum ORDER BY Timestamp DESC) AS rn
   FROM DynamicText
)
SELECT *
FROM cte
WHERE rn = 1
Shiva
  • 20,575
  • 14
  • 82
  • 112
0

Take a look at this question and answer:

Efficiently select top row for each category in the set

You can do a sub-query to get the enum/timestamp pair, and join that with your full row. That assumes you don't have duplicate timestamps for any given enum.

Community
  • 1
  • 1
Kendrick
  • 3,747
  • 1
  • 23
  • 41