SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oracle Query Offset

    HOW DO YOU OFFSET A RECORDSET WITH ORACLE???

    I thought Oracle was the daddy of all databases but I can't find a way of offseting the queries I pull back. Does anyone know how I can do it or supply some articles/examples/tutorials???

    Cheers for helping me destress!
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    offset? please explain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry if I'm unclear...

    If I have a list of 100 records, I want to show 10 records at a time. So the first stage is to limit the amount of records retrieved...

    Secondly, how do I view records from 11-20, 21-30 or 51-60? Does this make sense? You would think there would be some sort of facility for this in Oracle 9i considering this would be a facility used quite a lot.
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The only problem with ROWNUM is that it is defined before I do the ORDER, but I can't use ORDER BY in a sub-query...

    I'm trying to order the items by descending date and select only the first 5 records:

    Code:
     select rownum, news_id, title, created_date  from 
     	(select news_id, title, created_date from wb_news_items
     	where title LIKE '%new%'
     	order by created_date desc
     	)
     where rownum <= 5
    ... won't work!!
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what does "won't work" mean? syntax error? doesn't run? runs but returns 0 rows? runs but returns more than 5 rows? runs but returns 5 rows in the wrong order? runs but returns the wrong 5 rows?

    try this:
    Code:
    select news_id, title, created_date 
      from (
           select rownum as rnum
                , news_id, title, created_date  
             from ( 
                  select news_id, title, created_date 
                    from wb_news_items
                   where title LIKE '%new%'
                  order 
                      by created_date desc
                  ) as q1
           ) as q2
     where rnum <= 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
        1  select news_id, title, created_date
        2	from (
        3		 select rownum as rnum
        4			  , news_id, title, created_date
        5		   from (
        6			    select news_id, title, created_date
        7				  from wb_news_items
        8				 where title LIKE '%new%'
        9				order
       10				    by created_date desc
       11				) as q1
       12		 ) as q2
       13*  where rnum <= 5
      SQL> /
      			  order
      			  *
      ERROR at line 9:
      ORA-00907: missing right parenthesis
    Its falling over with the ORDER BY clause within a sub-query.
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hmmm

    no idea

    perhaps you could switch to a different database that does allow ORDER BY in a subquery

    a ha ha ha ha ha

    just kidding
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, the problem I had was running the command on an old version of Oracle without realising... I'm now on 9i. So now the ORDER BY clause works in a sub-query but the ROWNUM part is causing some issues.

    While a "WHERE ROWNUM <= 10" works fine, putting a "ROWNUM > 5" in doesn't. Is there any known issues with using this method?
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ROWNUM or the alias RNUM? you should not be testing ROWNUM directly

    and again, what does "doesn't work" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've solved it now actually... for some reason Oracle 9i doesn't like the "AS q1" or "AS q2" parts. I just removed the AS's (take that quote however you want )
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    whoa, i didn't know that, thanks for the update
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    OK, why are you using a subquery at all?

    Unless there are a TON of rows that query returns, I don't really see the benefit of doing it like this.

    A query like this won't work? It should because the rownum is created on the fly and is a reference to the resulting recordset. At least that's what I was always told....

    Code:
    SELECT news_id, title, created_date
    FROM wb_news_items
    WHERE title like '%new%'
    AND rownum <= 5
    ORDER BY created_date DESC
    BTW, the AS only works on aliasing fields, not on tables. And in your case, I don't see the benefit of aliasing the tables if you're not planning on referencing them anywhere else in your query. You only really need them in the case where there are two fields in the same query level and you need to specify a relationship or a quantitative condition.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  14. #14
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When we tested it, the ROWNUM is defined before the ORDER BY clause. That's why it was causing some beef!
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.


  15. #15
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    If this is for an ASP app, why not use the recordset object's built-in paging methods?

  16. #16
    Also available in Large Si's Avatar
    Join Date
    Sep 2002
    Location
    Walsall, UK
    Posts
    1,911
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to use minimal resources for it. Surely retrieving all records into an object is less resourceful than retrieving only the records I'm interested in...
    Si
    Are you a Photoshop Jedi Master? Prove it!

    Is funky house your bag? You'll love this!

    Voice
    , eyes, ears, body and hands.



Bookmarks

Posting Permissions

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