SQL: Select single item per name with multiple criteria

Question

I'm trying to select a single item per value in a "Name" column according to several criteria.

The criteria I want to use look like this:

1. Only include results where IsEnabled = 1
2. Return the single result with the lowest priority (we're using 1 to mean "top priority")
3. In case of a tie, return the result with the newest Timestamp

I've seen several other questions that ask about returning the newest timestamp for a given value, and I've been able to adapt that to return the minimum value of Priority - but I can't figure out how to filter off of both Priority and Timestamp.

Here is the question that's been most helpful in getting me this far.

Sample data:

+------+------------+-----------+----------+
| Name |  Timestamp | IsEnabled | Priority |
+------+------------+-----------+----------+
|  A   | 2018-01-01 |     1     |    1     |
|  A   | 2018-03-01 |     1     |    5     |
|  B   | 2018-01-01 |     1     |    1     |
|  B   | 2018-03-01 |     0     |    1     |
|  C   | 2018-01-01 |     1     |    1     |
|  C   | 2018-03-01 |     1     |    1     |
|  C   | 2018-05-01 |     0     |    1     |
|  C   | 2018-06-01 |     1     |    5     |
+------+------------+-----------+----------+

Desired output:

+------+------------+-----------+----------+
| Name |  Timestamp | IsEnabled | Priority |
+------+------------+-----------+----------+
|  A   | 2018-01-01 |     1     |    1     |
|  B   | 2018-01-01 |     1     |    1     |
|  C   | 2018-03-01 |     1     |    1     |
+------+------------+-----------+----------+

What I've tried so far (this gets me only enabled items with lowest priority, but does not filter for the newest item in case of a tie):

SELECT DATA.Name, DATA.Timestamp, DATA.IsEnabled, DATA.Priority
From MyData AS DATA
INNER JOIN (
  SELECT MIN(Priority) Priority, Name
  FROM MyData
  GROUP BY Name
) AS Temp ON DATA.Name = Temp.Name AND DATA.Priority = TEMP.Priority
WHERE IsEnabled=1

Here is a SQL fiddle as well.

How can I enhance this query to only return the newest result in addition to the existing filters?

Answer 1

Use row_number():

select d.*
from (select d.*,
             row_number() over (partition by name order by priority, timestamp) as seqnum
      from mydata d
      where isenabled = 1
     ) d
where seqnum = 1;

Answer 2

The most effective way that I've found for these problems is using CTEs and ROW_NUMBER()

WITH CTE AS(
  SELECT *, ROW_NUMBER() OVER( PARTITION BY Name ORDER BY Priority, TimeStamp DESC) rn
  FROM MyData
  WHERE IsEnabled = 1
  )
SELECT Name, Timestamp, IsEnabled, Priority
From CTE
WHERE rn = 1;