Ari,
I love your web page and visit quite often. One question:
How do I select just the EVEN or ODD records in a table?
Thanks,
Kishore
---------------------------------------------------------------------------
(see additional comments from Bob Smith and John Lennon following Ari's
response)
Kishore,
Glad that you like the web page.
As for your question, you can do it in 2 ways:
1) Write a PL/SQL routine to open up a cursor (SELECT * FROM TABLE_NAME;).
When looping through the records, output the row only if it is
1,3,5,7,... or 2,4,6,8,... You can use a variable to keep track of the
row number.
2) If you have a sequenced primary key, do
SELECT * FROM TABLE_NAME WHERE
mod(primary_key_column,2) = 0;
for even numbers. For odd numbers, do:
SELECT * FROM TABLE_NAME WHERE
mod(primary_key_column,2) = 1;
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 275+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Just found your site, and appreciate the time you take to pass along these
tips. I did have one comment on tip 295 -- Selecting only even or odd
records. If the table does not have a sequenced primary key, you can create
one with a subquery.
SELECT username
FROM (SELECT rownum num, username
FROM dba_users
)
WHERE MOD(num,2) = 0;
I realize that relational theory does not place an order on records, but
this structure might be useful in some cases.
Thanks,
Robert(Bob) Smith
Crown Equipment Corporation
(419) 629-2220 x2070 [Phone]
(419) 629-9233 [Fax]
bob.smith@crown.com
---------------------------------------------------------------------------
SQL> desc member_list
Name Null? Type
------------------------------- -------- ----
E_MAIL VARCHAR2(50)
NAME VARCHAR2(50)
How do I select just the EVEN or ODD records in a table?
select e_mail from (select rownum row_num, e_mail from member_list)
where mod(row_num,2) = 0 -- EVEN
select e_mail from (select rownum row_num, e_mail from member_list)
where mod(row_num,2) = 1 -- ODD
Regards
John C. Lennon
Database Consultants Inc.
7201 W Lake Mead Blvd, Suite 203
Las Vegas NV 89128
Tel: (702) 498 4990
Fax: (702) 871 4318
e-mail: johnlennon@ieee.org
Website: http://members.aol.com/jomarlen/
Back to Ari Kaplan's Home Page