Home » SQL & PL/SQL » SQL & PL/SQL » what's wrong with this query (oracle 10g)
what's wrong with this query [message #398970] Mon, 20 April 2009 01:14 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi what is wring with below query.actually i want to retrieve records from 100 to 200
from 300 to 600------somethng like that.
but with below query i am getting first 40 records only.

SELECT EMR_SNTP_ID,
  ERROR_DATETIME
FROM
  (SELECT EMR_SNTP_ID,
    ERROR_DATETIME
  FROM
    (SELECT EMR_SNTP_ID,
      ERROR_DATETIME
    FROM
      (SELECT EMR_SNTP_ID,
        ERROR_DATETIME
      FROM EMRSNTPLOGS
      WHERE STATUS = 1
      ORDER BY EMR_SNTP_ID ASC
      )
    WHERE rownum <= 40
    )
  ORDER BY EMR_SNTP_ID
  )
WHERE rownum <= 17905

Re: what's wrong with this query [message #398972 is a reply to message #398970] Mon, 20 April 2009 01:23 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>but with below query i am getting first 40 records only.
Because that is what you are asking for by the following!
>WHERE rownum <= 40
Re: what's wrong with this query [message #398975 is a reply to message #398972] Mon, 20 April 2009 01:46 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

On ROWNUM and Limiting Results
Re: what's wrong with this query [message #398987 is a reply to message #398975] Mon, 20 April 2009 03:10 Go to previous messageGo to next message
lraok
Messages: 5
Registered: April 2009
Location: Bangalore
Junior Member
please tell me what exactly you want to retreive.

-Lakshman
Re: what's wrong with this query [message #398991 is a reply to message #398987] Mon, 20 April 2009 03:40 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah i got it
i did like this

SELECT * FROM (SELECT A.*,ROWNUM RNUM FROM (SELECT EMR_SNTP_ID, ERROR_DATETIME
      FROM EMRSNTPLOGS
      WHERE STATUS = 1
      ORDER BY EMR_SNTP_ID ASC) A WHERE ROWNUM<=17905 ) WHERE RNUM>=148;
Re: what's wrong with this query [message #398994 is a reply to message #398991] Mon, 20 April 2009 03:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Using row_number() is more elegant:

select col1
,      col2
from   (select tab.*
        ,      row_number() over (order by col3) as rn
        from   tab
       )
where  rn between 123 and 234


Not sure though if the stopkey for rownum might be more efficient. You'll have to find that out by comparing plans

[Updated on: Mon, 20 April 2009 03:44]

Report message to a moderator

Previous Topic: substring up to 5 characters after removing special characters
Next Topic: Heirarchical Query, Connect By Loop in User Data
Goto Forum:
  


Current Time: Tue Dec 06 12:31:37 CST 2016

Total time taken to generate the page: 0.09611 seconds