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 ...

What do you mean with "first result"? There is only one maximum hittime per partition. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 23, 2013 at 12:50
this window query will return me the whole table, when I am only interested in the max result. Distinct, as suggested by @amirreza-keshavarz helps in this case. — JohnDoe
– JohnDoe, Commented Nov 23, 2013 at 17:12
What i can't see why you don't want to use the GROUP BY. And both your second query and Amirezza's have a redundant order by hittime desc — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 23, 2013 at 17:15
My question may not have been specific enough. The GROUP BY works without problems, but what to do when I want eg. max and the second largest? — JohnDoe
– JohnDoe, Commented Nov 23, 2013 at 21:15

Noel · Accepted Answer · 2013-11-24 02:34:36Z

10

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

answered Nov 24, 2013 at 2:34

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amir Keshavarz · Accepted Answer · 2013-11-23 12:46:59Z

2

Use distinct statement.

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

answered Nov 23, 2013 at 12:46

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

Collectives™ on Stack Overflow

Limit result set in sql window function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related