Re: chosing id of max values
- From: Remigiusz Sokolowski <rems@xxxxxxxx>
- To: amwilson@xxxxxxxxxxxx
- Date: Wed, 25 Jul 2007 11:03:54 +0200
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
Other related posts:
