Locating most current record using ROW_NUMBER() vs. Index Full Scan (Min/Max)

Very often when reading data, we need to locate the most current record for a set of conditions. One typical example is ...