Home » SQL & PL/SQL » SQL & PL/SQL » SQL Request...
SQL Request... [message #204513] Tue, 21 November 2006 02:20 Go to next message
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
Re: SQL Request... [message #204514 is a reply to message #204513] Tue, 21 November 2006 02:31 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: SQL Request... [message #204516 is a reply to message #204513] Tue, 21 November 2006 02:35 Go to previous messageGo to next message
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
Re: SQL Request... [message #204519 is a reply to message #204513] Tue, 21 November 2006 02:44 Go to previous message
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:
  


Current Time: Sat Dec 03 06:21:09 CST 2016

Total time taken to generate the page: 0.23333 seconds