chosing id of max values

From: Remigiusz Sokolowski <rems_at_wp-sa.pl>
Date: Tue, 24 Jul 2007 15:58:32 +0200
Message-id: <46A60588.2070900@wp-sa.pl>

Problem is simple - I need to get id (and other values by which I can not group) of rows with some maximum value. Below two examples, which do the thing

any suppositions to use a "better"/other way to achieve this? some kind of trick with analytic/aggregate function or anything else?

thanks in advance
Remigiusz

select r.id, r.wid, r.start_time, b.wid from ( select id, start_time, wid
FROM wptv_listings l join (
select channel, max(start_time) stime from wptv_listings WHERE visible='T' AND start_time BETWEEN <date1> AND <date2> group by channel
) m on l.CHANNEL=m.channel and l.START_TIME=m.STIME
) r JOIN wptv_blocks b on r.id=b.id

select r.id, r.wid, r.start_time, b.wid from ( select id, start_time, wid, rank() over (partition by channel order by start_time desc) rank1
FROM wptv_listings l
WHERE visible='T' AND start_time BETWEEN <date1> AND <date2>
) r JOIN wptv_blocks b on r.id=b.id

where r.rank1=1

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

Received on Tue Jul 24 2007 - 08:58:32 CDT