I do something like this every single day - it would be one of my most common queries:
select * from
(select columns, rownumber() over (partition by something) as number from whatever) x
where x = 1
Which is horrible and seems like there's a lot of extra stuff that shouldn't be needed - a subselect, an alias for the sub select, another select statement - and at best you end up with an extra column in your output - at worst you have to re-list a bunch of columns.