Home » SQL & PL/SQL » SQL & PL/SQL » How to get First 100 odd Numbers using Rownum
How to get First 100 odd Numbers using Rownum Thu, 17 May 2007 23:50
 kumar_dln Messages: 5Registered: October 2005 Location: Philadelphia Junior Member
Hi All,

How to get first 100 odd numbers using rownum concept?

This is an interview question.
Re: How to get First 100 odd Numbers using Rownum [message #238423 is a reply to message #238420] Fri, 18 May 2007 00:00
 BlackSwan Messages: 25531Registered: January 2009 Location: SoCal Senior Member
use modulo function.
Re: How to get First 100 odd Numbers using Rownum [message #238433 is a reply to message #238420] Fri, 18 May 2007 00:56
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

MOD

Regards
Michel
Re: How to get First 100 odd Numbers using Rownum [message #238435 is a reply to message #238423] Fri, 18 May 2007 01:08
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Use "Reply" button and not "Report a message to a moderator" link.
Original message:
 Quote: Hi anacedent, Could you confirm me that Is this the correct query for listing first 100 odd numbers? SELECT rno FROM (SELECT ROWNUM rno FROM all_objects WHERE ROWNUM BETWEEN 1 AND 200) WHERE MOD(rno,2)=1;

This is one correct way.

Regards
Michel

Re: How to get First 100 odd Numbers using Rownum [message #238437 is a reply to message #238433] Fri, 18 May 2007 01:09
 Littlefoot Messages: 21127Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator
 Reported message Reported By: kumar_dln On: Fri, 18 May 2007 07:59 In: SQL & PL/SQL » SQL & PL/SQL Newbies » How to get First 100 odd Numbers using Rownum Reason Hi anacedent, Could you confirm me that Is this the correct query for listing first 100 odd numbers? SELECT rno FROM (SELECT ROWNUM rno FROM all_objects WHERE ROWNUM BETWEEN 1 AND 200) WHERE MOD(rno,2)=1;

@kumar_dln, please, do not report your own messages - those reports are visible only to the administration staff. Whatever you have to say about your question, say in a topic you have opened.
Re: How to get First 100 odd Numbers using Rownum [message #238438 is a reply to message #238435] Fri, 18 May 2007 01:13
 kumar_dln Messages: 5Registered: October 2005 Location: Philadelphia Junior Member
Hi,

Thanks for quick response.

Do we have another way with out using all_objects?
Re: How to get First 100 odd Numbers using Rownum [message #238439 is a reply to message #238438] Fri, 18 May 2007 01:14
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```SQL> select rownum from dual connect by level <= 5;
ROWNUM
----------
1
2
3
4
5

5 rows selected.
```

Regards
Michel
Re: How to get First 100 odd Numbers using Rownum [message #238440 is a reply to message #238439] Fri, 18 May 2007 01:20
 kumar_dln Messages: 5Registered: October 2005 Location: Philadelphia Junior Member
Hi Michel,

Nice to get result from Dual and level itself.
Re: How to get First 100 odd Numbers using Rownum [message #238443 is a reply to message #238423] Fri, 18 May 2007 01:25
 caliguardo Messages: 107Registered: February 2007 Location: Chennai Senior Member
Can u try this!!

CREATE TABLE ODDFIND (NUM VARCHAR2(20));

CREATE SEQUENCE ODDFINDSEQ START WITH 1 INCREMENT BY 1

BEGIN
FOR I IN 1 ..200 LOOP
INSERT INTO ODDFIND VALUES(ODDFINDSEQ.NEXTVAL);
END LOOP;
COMMIT;
END;

"

SELECT NUM FROM (SELECT A.*,ROWNUM S FROM ODDFIND A) WHERE MOD(S,2) != 0

"
Re: How to get First 100 odd Numbers using Rownum [message #238449 is a reply to message #238443] Fri, 18 May 2007 02:03
 Maaher Messages: 7062Registered: December 2001 Senior Member
 caliguardo wrote on Fri, 18 May 2007 08:25 Can u try this!! CREATE TABLE ODDFIND (NUM VARCHAR2(20)); CREATE SEQUENCE ODDFINDSEQ START WITH 1 INCREMENT BY 1
Increment by 2 and you only have odd numbers

MHE
Re: How to get First 100 odd Numbers using Rownum [message #238452 is a reply to message #238449] Fri, 18 May 2007 02:13
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
In this case no need of a work table:
```SQL> create sequence s start with 1 increment by 2;

Sequence created.

SQL> select s.nextval from dual connect by level <= 10;
NEXTVAL
----------
1
3
5
7
9
11
13
15
17
19

10 rows selected.
```

Regards
Michel
Re: How to get First 100 odd Numbers using Rownum [message #238521 is a reply to message #238452] Fri, 18 May 2007 07:17
 Frank Messages: 7880Registered: March 2000 Senior Member
No need for a sequence:
```SQL> select 2 * (rownum - 1) + 1 from (select * from dual connect by level < 50);

2*(ROWNUM-1)+1
--------------
1
3
5
7
9
11
13
15
17
19
21
23
25
27
29
31
...
```
Re: How to get First 100 odd Numbers using Rownum [message #238533 is a reply to message #238420] Fri, 18 May 2007 08:13
 srmunnangi Messages: 7Registered: March 2005 Junior Member
SELECT rn
FROM (SELECT ROWNUM rn
FROM DUAL
CONNECT BY LEVEL <= 200)
WHERE MOD (rn, 2) = 1
Re: How to get First 100 odd Numbers using Rownum [message #238534 is a reply to message #238521] Fri, 18 May 2007 08:20
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Frank,

Of course no need of a sequence, this is what we said in the first answers (first with all_objects then with dual).
The next answers explore other ways to do it.

Regards
Michel
Re: How to get First 100 odd Numbers using Rownum [message #238536 is a reply to message #238533] Fri, 18 May 2007 08:21
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Hi Sudhakara Rao Munnangi,

Welcome on this forum.

Regards
Michel
Re: How to get First 100 odd Numbers using Rownum [message #238576 is a reply to message #238534] Fri, 18 May 2007 10:51
 Frank Messages: 7880Registered: March 2000 Senior Member
 Michel Cadot wrote on Fri, 18 May 2007 15:20 Frank, Of course no need of a sequence, this is what we said in the first answers (first with all_objects then with dual). The next answers explore other ways to do it. Regards Michel

I know, but all previous solutions either used rowgenerators that generated more rows than needed (those were filtered out in a where-clause) (the 'mod' solutions) or, building on caliguardo's rather ridiculous solution, used unnecessary objects.

[Updated on: Fri, 18 May 2007 10:52]

Report message to a moderator

Re: How to get First 100 odd Numbers using Rownum [message #238579 is a reply to message #238576] Fri, 18 May 2007 10:56
 Michel Cadot Messages: 65084Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Frank, you're right (about the "too much rows").

Regards
Michel

Re: How to get First 100 odd Numbers using Rownum [message #238638 is a reply to message #238536] Fri, 18 May 2007 15:53
 srmunnangi Messages: 7Registered: March 2005 Junior Member
Hi Michel,