Limit result set in sql window function

Question

Assume I would like to rewrite the following aggregate query

select id, max(hittime)
from status
group by id

using an aggregate windowing function like

select id, max(hittime) over(partition by id order by hittime desc) from status

How can I specify, that I am only interested in the first result within the partition?

EDIT: I was thinking that there might be a solution with [ RANGE | ROWS ] BETWEEN frame_start AND frame_end. What to get not only max(hittime) but also the second, third ...

Answer 1

I think what you need is a ranking function, either ROW_NUMBER or DENSE_RANK depending on how you want to handle ties.

select id, hittime
from (
     select id, hittime,
            dense_rank() over(partition by id order by hittime desc) as ranking
     from status
     ) as x
where ranking = 1;  --to get max hittime
--where ranking <=2;  --max and second largest

Answer 2

Use distinct statement.

select DISTINCT id, max(hittime) over(partition by id order by hittime desc) from status