Re: chosing id of max values

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Tue, 24 Jul 2007 18:21:54 +0300
Message-ID: <6e49b6d00707240821y1af86cd7nae52895481d310d3@mail.gmail.com>

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

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

Received on Tue Jul 24 2007 - 10:21:54 CDT