0

How to build a query that returns exactly n number of rows?

A single row could be retrieved by select x from dual. What about n rows?

AllOutOfSalt
  • 1,516
  • 3
  • 27
  • 46

1 Answers1

6

Please try below query to generate 100 numbers ranging from 1 to 100:

select level 
from dual 
connect by level <= 100
TechDo
  • 18,398
  • 3
  • 51
  • 64
  • The query should probably have an `ORDER BY` clause so you know *which* first `n` rows you're getting back. Otherwise, it's essentially `n` random rows if you're actually select from a table. – jpmc26 Sep 10 '13 at 06:52
  • 2
    No it's not @jpmc26; this is a hierarchical query on a one row table that returns the number of rows in the hierarchy. It's guaranteed to have exactly N rows. – Ben Sep 10 '13 at 06:55
  • Exactly Ben. @jpmc26, I tried query in Oracle 11G and working fine without an `order by` clause. – TechDo Sep 10 '13 at 07:03
  • thanks, worked just fine. But i don't know why i got a downvote! – AllOutOfSalt Sep 10 '13 at 07:12
  • @Ben I'm not referring to the number of rows returned; I'm referring to which rows are returned in a table with more than 100 rows. See [this answer](http://stackoverflow.com/a/900106/1394393). So you don't know which rows you're getting. It might be the last 100, or the first 100, or some 100 in the middle. If you don't care which 100 rows, that's fine. Or am I misunderstanding the question? – jpmc26 Sep 10 '13 at 08:34
  • I assumed from the question that the OP wanted a row generator @jpmc26. – Ben Sep 10 '13 at 08:37