Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: use of COUNT() with "paging" (ie. where rownum between n and m)

    Here's the situation.

    I want to run the following query, and I need to know 1.) how many records TOTAL match the query (), and then get a list of values 1-250.

    For instance, lets say i have 5000 records match the internal query. I need to know there are 5k, and i need to get the actual data for rownum's 1-250.

    Without resorting to a second query, is there an efficient way to do this?

    Select subQ.* FROM (
    SELECT rownum rn,a.* FROM (
    /*-----------------------------------------------------------*/
    /*-----------------------------------------------------------*/
    SELECT
    account
    from customer
    order by account

    /*-----------------------------------------------------------*/
    /*-----------------------------------------------------------*/
    ) a
    ) subQ

    WHERE subQ.rn
    BETWEEN 1 AND 250

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Without a second query? Why not?

    Code:
    Select subQ.* FROM (
    SELECT rownum rn,a.* FROM (
    /*-----------------------------------------------------------*/
    /*-----------------------------------------------------------*/
    SELECT
    t.account, v.cnt 
    from customer t, (select count(*) as cnt from customer) v
    order by t.account
    
    /*-----------------------------------------------------------*/
    /*-----------------------------------------------------------*/
    ) a
    ) subQ
    
    WHERE subQ.rn 
    BETWEEN 1 AND 250
    Don't know how fast that'll run, but without including a join between t & v, you'll get a Cartesian product with the total count of records showing up on each row. ---=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •