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@xxxxxxxx> 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)
-- //www.freelists.org/webpage/oracle-l