Re: chosing id of max values

Anthony Wilson wrote:
> Maxim's solution was almost correct: this is exactly what FIRST and LAST are for.
> However I find using them as aggregates rather than analytics works nicely:
>
> select max(l.id) keep (dense_rank last order by l.start_time) latest_id
> , max(l.wid) keep (dense_rank last order by l.start_time) latest_listing_wid
> , max(l.start_time) latest_start_time
> , max(b.wid) keep (dense_rank last order by l.start_time) latest_block_wid
> from wptv_listings l
> join wptv_blocks b on l.id = b.id
> where l.visible = 'T'
> and l.start_time between :date1 and :date2
> group by l.channel;
>
> One caveat: what should happen when different records are ranked the same with
> respect to the start_time?
>
> cheers,
> Anthony
>

well, in this particular case (TV program) it is not possible, so I do not consider this.

-- 
---------------------------------------------------------------------
Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x

Zastrzezenie:
Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora 
i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa 
Wirtualna Polska S.A.
---------------------------------------------------------------------



WIRTUALNA  POLSKA  SA, ul. Traugutta 115c, 80-226 Gdansk; NIP: 957-07-51-216; 
Sad Rejonowy Gdansk-Polnoc KRS 0000068548, kapital zakladowy 62.880.024 zlotych (w calosci wplacony)
--
http://www.freelists.org/webpage/oracle-l