Re: chosing id of max values
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: rems@xxxxxxxx
- Date: Tue, 24 Jul 2007 18:21:54 +0300
A simple max(start_time) over (partition by channel) won't work?
As for example for dba_objects:
SQL> desc dba_objects
Name
-------------------------------------------
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
SQL> ed
Wrote file afiedt.buf
1 select substr(OBJECT_NAME, 1, 10), owner, max(created) over
(partition by owner)
2* from dba_objects
SQL> /
SUBSTR(OBJECT_NAME,1,10) OWNER
MAX(CREATE
----------------------------------------
------------------------------ ----------
EXF$JAVAMS EXFSYS
2005.08.30
EXF$ATTRSE EXFSYS
2005.08.30
DUPL_ATTRS EXFSYS
2005.08.30
EMPACCOUNT GINTS
2007.07.24
PROC1 GINTS
2007.07.24
PRC GINTS
2007.07.24
A GINTS
2007.07.24
LB.REGRESS GINTS
2007.07.24
PARODYOFBI GINTS
2007.07.24
BIG GINTS
2007.07.24
Gints Plivna
http://www.gplivna.eu
2007/7/24, Remigiusz Sokolowski <rems@xxxxxxxx>:
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@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:
