Re: How do I return N results per page + total number of results

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 13 Jan 2000 18:05:03 -0500
Message-ID: <43ms7s8kuf8fk2e1e69tl1tun4dcrjlh5a@4ax.com>

On Thu, 13 Jan 2000 10:27:26 -0800, BoulderDavid <dbenson_at_alt.net> wrote:

>I'm able to return search results w/ N results per page. What I'd like
>to be able to add is text similar to: Showing results 20-29 of 125
>results. How can I return the total number for results w/o executing
>the query twice?
>
>Here's my current query:
>select * from (
> select rownum rn, Q.* from (
> select id, type, name
> from mytbl
> where content (name, '$<search_key>') > 0
> order by type, name
> ) Q)
>where rn between 20 and 29

In 8i you can select in a select clause so things like this are possible...

select V.*, ( select count(*) from mytbl ) total_rows from (   select rownum rn, Q.* from (
    select id, type, name

      from mytbl
     where content (name, '$<search_key>') > 0
     order by type, name

  ) Q) V
where rn between 20 and 29

hope this helps.

chris.

>
>I've tried adding max, count functions at various points in the query,
>but they won't work since they are group functions and I'm attempting
>to combine them w/ multiple rows.
>
>Thanks,
>
>David
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 13 2000 - 17:05:03 CST