Home » Other » Training & Certification » records between rownum
records between rownum [message #298410] Wed, 06 February 2008 01:28 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Guys,

I want to find the number of records which fall between 100 and 150 rownum..how do I do it?

I tried the following query

select NO from count1 where rownum<=150 and NO not in (select NO from count1 where rownum<=100);

I getting all the 150 records. What query should I run?

- Das
Re: records between rownum [message #298419 is a reply to message #298410] Wed, 06 February 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course this is a homework as this is meaningless.
Use inline view, select the first 150 rows and then restrict to those with (inline) rownum greater than 99.

Forgot: please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 06 February 2008 01:39]

Report message to a moderator

Re: records between rownum [message #298433 is a reply to message #298410] Wed, 06 February 2008 01:59 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Always, always, always format the code that you are providing. No matter how small a snippet.
This question is asked on a regular basis. There are various ways to do what you need.
One such way would be
(Pseudo code)
SELECT columns
FROM (SELECT rownum rn, columns
      FROM table
      ORDER BY column(s)
     )
WHERE rn BETWEEN m AND n;

Analytic Functions such as RANK, DENSE_RANK and ROW_NUMBER are also commonly used in this situation.
Previous Topic: After passing the OCA exam !!!
Next Topic: Need information to write Oracle 10g OCP exam
Goto Forum:
  


Current Time: Tue Dec 06 02:25:30 CST 2016

Total time taken to generate the page: 0.23728 seconds