Getting rows N through M of a result set using GROUP BY and ORDER BY pl/sql

Question

I need to convert following query to return the specific number of rows from any LOWERBOUND to UPPERBOUND (any N to M).

SELECT * FROM (
  SELECT 
    OWNER||','||HOSTNAME OWNER,
    SubStr(FILENAME,InStr(FILENAME,'/',-1)+1,Length(FILENAME)-InStr(FILENAME,'/',-1)+1)  a, 
    To_Date(SubStr(FILENAME,InStr(filename,'_',1)+1,14),'YYYYMMDDHH24MISS') STARTIME,
    servergrp Server_Group,
    filename, 
    Count(1) b  
  FROM patcheventlog  
  GROUP BY 
    OWNER||','||HOSTNAME ,
    SubStr(FILENAME,InStr(FILENAME,'/',-1)+1,Length(FILENAME)-InStr(FILENAME,'/',-1)+1)  ,  
    To_Date(SubStr(FILENAME,InStr(filename,'_',1)+1,14),'YYYYMMDDHH24MISS') ,
    servergrp,
    filename  
  order by 
    Startime desc 
)
where  
  rownum<=10

By using the aboove rownum concept i can only get the result set from first row to the rownum defined. As above from 1 to 10.

So please help me with this.

Thanks

@anishroniyar..insetad of rownum use row_number() over partition by ... — anwaar_hell, Nov 02 '15 at 08:18

score 0 · Accepted Answer · edited Nov 02 '15 at 08:51

0

Sample for reference. We need to do the following:

select * from (
select OWNER,a,STARTIME,Server_Group,filename,b ,row_number() over (order by STARTIME desc ) rnm from
(
  SELECT 
    OWNER||','||HOSTNAME OWNER,
    SubStr(FILENAME,InStr(FILENAME,'/',-1)+1,Length(FILENAME)-InStr(FILENAME,'/',-1)+1)  a, 
To_Date(SubStr(FILENAME,InStr(filename,'_',1)+1,14),'YYYYMMDDHH24MISS') STARTIME,
    servergrp Server_Group,
    filename, 
    Count(1) b  
  FROM patcheventlog  
  GROUP BY 
    OWNER||','||HOSTNAME ,
    SubStr(FILENAME,InStr(FILENAME,'/',-1)+1,Length(FILENAME)-InStr(FILENAME,'/',-1)+1)  ,  
    To_Date(SubStr(FILENAME,InStr(filename,'_',1)+1,14),'YYYYMMDDHH24MISS') ,
    servergrp,
    filename 
   ORDER BY 
    Startime desc )
) WHERE rnm BETWEEN 2 AND 7

edited Nov 02 '15 at 08:51

anishroniyar

96
12

answered Nov 02 '15 at 08:21

anwaar_hell

756
5
23

thanks for the query but this query is setting all the values of rnm column to 1. It doesn't set the rnm column serially. So anything in this? – anishroniyar Nov 02 '15 at 08:36
@anishroniyar..actually i have used all columns in partition clause ..to generate row_number serially use column in partition clause whose value is same in all rows... – anwaar_hell Nov 02 '15 at 08:47
yes i have edited it. Thanks for your help friend. – anishroniyar Nov 02 '15 at 08:48
@anishroniyar..here is fiddle for direction...http://sqlfiddle.com/#!4/67833/1 – anwaar_hell Nov 02 '15 at 08:49
@anishroniyar..glad to help you..:) – anwaar_hell Nov 02 '15 at 08:51
thanks man i got it. – anishroniyar Nov 02 '15 at 08:52
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93955/discussion-between-anishroniyar-and-anwaar-hell). – anishroniyar Nov 02 '15 at 08:53

Getting rows N through M of a result set using GROUP BY and ORDER BY pl/sql

1 Answers1