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 [message #238420] Thu, 17 May 2007 23:50 Go to next message
kumar_dln
Messages: 5
Registered: 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 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account 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 Go to previous messageGo to next message
kumar_dln
Messages: 5
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
kumar_dln
Messages: 5
Registered: October 2005
Location: Philadelphia
Junior Member

Hi Michel,

Thanks for reply.

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 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: 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 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: 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 Wink

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: 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 Go to previous messageGo to next message
srmunnangi
Messages: 7
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank, you're right (about the "too much rows"). ./fa/1578/0/

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 Go to previous message
srmunnangi
Messages: 7
Registered: March 2005
Junior Member

Hi Michel,

Thanks for your support and answers.

Thanks
Sudhakar
Previous Topic: EXISTS/NOT EXIST/ NOT IN/MINUS
Next Topic: Trimming unseen characters at the end of a string
Goto Forum:
  


Current Time: Tue Dec 06 10:39:43 CST 2016

Total time taken to generate the page: 0.07139 seconds