Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Values not retireved using rownum in oracle

 
Prashanth Chandra
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I have writte a query which retirves data from table using the rownum attribute which works fine:

select * from (select id,count(*) from average a where rownum <= (select count(*) from average) group by id) where rownum < 65501

But when I am trying to retieve the value above 65501 I am not able to fetch the records from the table. Please fine the below which is not working

select * from (select id,count(*) from average a where rownum <= (select count(*) from average) group by id) where >= 65501

Can anyone please let me know how I can retireve the data based on the above query.

Thanks in advance
regards
Prashanth
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Prashanth,
If I remember correctly, Oracle only allows less than or equal to logical operators with rownum pseudo-column.
Hence your query using greater than operator does not work.
I'm only guessing, but I think you may be able to use the "between" operator, as in:

Good Luck,
Avi.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is your original query (see the comments next to your code):



Run the following:



to find the total number of rows associated with the ID group by clause... but as written... you are always showing the numbers for all your rows grouped by ID.
[ October 30, 2008: Message edited by: Paul Campbell ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic