select with start, stop and rowcount, need total

Question

I have this oracle sql which gives me a subset of records for paging. I also need the total of records, can I combine the two queries ? The real query is much more complicated (union with where etc), I simplified it to not distract from my question.

select count(*) from table;
select outerfields.* from 
 (select row_number() over (order by id asc) r, innerfields.* from 
   ( (select * from table) innerfields)) outerfields where r between 1 and 10;

obviously something like the following doesn't work

select max(r), outerfields.* from 
  (select row_number() over (order by id asc) r, innerfields.* 
    from ( (select * from m30brief) innerfields)) outerfields where r between 1 and 10;

You can do this with "PARTITION BY". Have a look here: http://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword — lilalinux, Sep 19 '12 at 09:44
i read this but when i add the partition by to the over i get all the records, not a subset, could you answer with the modified query please ? — peter, Sep 19 '12 at 10:09
Is there something wrong with my answer? `Partition by` will not help here. — Nikola Markovinović, Sep 19 '12 at 10:22
@Nikola, no there isn't, it works and will accept it, only I hoped to learn a better technique from this suggestion to get the same result, but since you say i won't i believe you — peter, Sep 19 '12 at 10:25

score 1 · Accepted Answer · answered Sep 19 '12 at 09:59

You can add count(*) over() to outerfields derived table:

select outerfields.* 
  from 
  (
    select row_number() over (order by id asc) r, 
           count(*) over () totalRows,
           innerfields.* 
      from 
      (
        select * 
          from table
      ) innerfields
  ) outerfields
 where r between 1 and 10;

select with start, stop and rowcount, need total

1 Answers1