Re: chosing id of max values

  • From: Anthony Wilson <amwilson@xxxxxxxxxxxx>
  • To: Remigiusz Sokolowski <rems@xxxxxxxx>
  • Date: Wed, 25 Jul 2007 16:31:45 +0800

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

On Wed Jul 25 15:04 , Remigiusz Sokolowski  sent:

>Gints Plivna wrote:
>> A simple max(start_time) over (partition by channel) won't work?
>>
>May be I was too laconic
>I have thought about getting only value of rows, of which one particular 
>value is max for the whole group - so I am not interested in rows which 
>have this particular value less than max. "A simple max(start_time) over 
>(partition by channel)"  gives me all rows while I need only those which 
>start_time=max(start_time) over (partition by channel). Solutions I have 
>provided gives me exactly what I need, however I try to find more of 
>them, then compare them and choose one... :-)
>
>Regards
>Remigiusz
>
>
>-- 
>---------------------------------------------------------------------
>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
>
>)

--
//www.freelists.org/webpage/oracle-l


Other related posts: