records between rownum [message #298410] |
Wed, 06 February 2008 01:28 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
pablolee
Messages: 2882 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.
|
|
|