OraFAQ Forum: SQL & PL/SQL » SQL Request...

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Request...

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Request... [message #204513]

Tue, 21 November 2006 02:20

pw1975
Messages: 24
Registered: June 2006

Junior Member

Hi all,

my table consists of 5 Columns:

CREATE TABLE TEST
(
  T_STAMP         DATE                          NOT NULL,
  UNC_PATH        VARCHAR2(4000 CHAR)           NOT NULL,
  DOC_TYPE        VARCHAR2(100 CHAR)                NULL,
  FILECOUNT_AGGR  INTEGER                           NULL,
  FILESIZE_AGGR   NUMBER(22,3)                      NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

Example Data:

Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:37:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\abc', 'FORM', 15, 0.761);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:37:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\abc', 'SV', 1, 0.013);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:37:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\abc', 'PROT', 8, 0.702);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:27:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\xyz', 'PROT', 1322, 436.928);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:27:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\xyz', 'MAN', 863, 103.073);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:27:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\xyz', 'LOP', 856, 412.949);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/13/2006 20:27:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\xyz', 'MEDIA', 208, 145.6);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/15/2006 23:05:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\rtz', 'PN', 4, 0.086);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/15/2006 23:05:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\rtz', 'VL', 60, 66.764);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/15/2006 23:05:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\rtz', 'ART', 31, 0.301);
Insert into TEST
   (T_STAMP, UNC_PATH, DOC_TYPE, FILECOUNT_AGGR, FILESIZE_AGGR)
 Values
   (TO_DATE('11/15/2006 23:05:00', 'MM/DD/YYYY HH24:MI:SS'), '\\test\rtz', 'ZIEL', 70, 19.206);
COMMIT;

The Columns UNC_PATH and T_STAMP are Dimensions. What i want as a result is:

For every UNC_PATH and T_STAMP the two largest FILESIZE_AGGR rows. My Data has a lot more entry per UNC_PATH and T_STAMP, so i actually need the biggest 5 FILESIZE_AGGR rows for these two Dimensions. But the testdata is only 3 rows for each UNC and T_STAMP.

Thank you very much!

Cheers,
ph

Report message to a moderator

Re: SQL Request... [message #204514 is a reply to message #204513]

Tue, 21 November 2006 02:31

Maaher
Messages: 7065
Registered: December 2001

Senior Member

you mean something like this?

SELECT t_stamp
     , unc_path
     , filesize_aggr
FROM ( SELECT t_stamp
            , unc_path
            , filesize_aggr
            , RANK() OVER ( PARTITION BY t_stamp, unc_path ORDER BY filesize_aggr DESC ) r
       FROM   mhe_foo
     )
WHERE r <= 5
/

MHE

Report message to a moderator

Re: SQL Request... [message #204516 is a reply to message #204513]

Tue, 21 November 2006 02:35

pw1975
Messages: 24
Registered: June 2006

Junior Member

Hi!

just began to read about the RANK / Analytical Functions... Thank you very much, you solved my problem!

have a nice day,
Philipp

Report message to a moderator

Re: SQL Request... [message #204519 is a reply to message #204513]

Tue, 21 November 2006 02:44

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Thanks for the Create and Insert scripts - it's nice to know somebody out there reads the Sticky Cool

Is this what you're looking for?

  1  select unc_path,t_stamp,filesize_aggr
  2  from  (select unc_path
  3               ,t_stamp
  4               ,filesize_Aggr
  5               , row_number() over (partition by unc_path,t_stamp order by filesize_aggr desc) rnum
  6         from   test)
  7* where  rnum <= 2

UNC_PATH   T_STAMP   FILESIZE_AGGR
---------- --------- -------------
\\test\abc 13-NOV-06          .761
\\test\abc 13-NOV-06          .702
\\test\rtz 15-NOV-06        66.764
\\test\rtz 15-NOV-06        19.206
\\test\xyz 13-NOV-06       436.928
\\test\xyz 13-NOV-06       412.949

Drat - far too slow.

[Updated on: Tue, 21 November 2006 02:44]

Report message to a moderator

Previous Topic:	Last 6 values
Next Topic:	passing a clob data to a procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 02:19:27 CST 2024