-----Original Message-----
Hi Ari,
Iam a big fan of your homepage. Iam a oracle developer.I have a
question, hope you would reply me.Thanks in advance for your time.
Question :
I have a table with 5000 records in it. I need to query the 1st,
the 500th, and the 1000th rows. I tried using RowNum in a where clause,
I tried the following query :
SELECT * FROM table_name
WHERE ROWNUM=1 or ROWNUM=500 or ROWNUM=1000
ORDER BY ordering_column;
But the above query did not work. It is retrieving only 1st row and not
showing 500th and 1000th rows.
Can you please help me in solving my problem.
Thank you,
Durga
______________________ Reply ______________________________________________
(NOTE: See John Lennon's message after Ari's for another solution)
Your query is not working because of the nature of ROWNUM. The ROWNUM is really
a pseudo-column that Oracle generates each time the query is run for the RESULTS
of the query only, not the data in the table. Using it in a WHERE clause is
meaningful only if you have a LESS-THAN (<) clause, or you have WHERE ROWNUM=1,
or have ROWNUM=1 AND ROWNUM=2, and so on.
What you could do is make a PL/SQL routine. Open a cursor and use a variable as
a counter. Display the row only if the counter = 1, 500, or 1000.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 205+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
______________________ John Lennon's Reply _________________________________
SQL> desc member_list
Name Null? Type
------------------------------- -------- ----
E_MAIL VARCHAR2(50)
NAME VARCHAR2(50)
I have a table with 5000 records in it. I need to query the 1st,
the 500th, and the 1000th rows. I tried using RowNum in a where clause.
select e_mail from (select rownum row_num, e_mail from member_list)
where row_num in (1,500,1000)
Regards
John
------------------------------------------
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/
---------------------------------------------------------------------------------------------------
Vijay Jagdale's response:
From: VIJAY JAGDALE
To: akaplan@interaccess.com
Subject: Discussion on
http://homepage.interaccess.com/~akaplan/oracle/ari90898.html
First, let me take the time to congratulate you on your fabulous, fabulous web
site. I greatly appreciate the time you have taken to help out the Oracle
community.
In the above html file you mentioned there is no way to Display ROWNUM=1 or
ROWNUM=500 condition, other than a PL/SQL procedure.
However I found that the ROWNUM is assigned by the where condition, and then we
can use the GROUP-HAVING condition to "hoodwink" Oracle into submission!
In my example I first created a query from my example table:
SQL>select rownum, be from be where fiscal_yr=1999;
ROWNUM BE
-------------- --------------
1 44
2 45
3 46
4 1
5 2
6 3
7 4
8 5
9 7
10 8
11 9
12 10
13 11
14 12
15 13
16 14
17 15
18 16
19 17
20 18
21 19
22 20
23 21
24 22
25 23
26 24
27 25
28 26
29 27
30 28
31 29
32 30
33 31
34 32
35 33
36 34
37 35
38 36
39 37
40 38
Then I created the query:
SQL>select rownum, max(be) from be
where fiscal_yr=1999
group by rownum
having rownum=2 or rownum=4 or rownum=8 or rownum=16 or rownum=32;
ROWNUM MAX(BE)
--------- --------------
--------- --------------
2 45
4 1
8 5
16 14
32 30
Yee Hah! It works like a Charm. Spread the word, including to "Durga" who
originally asked the question.
regards,
---------------------------------------------
Vijay Jagdale
Technical Systems Specialist
Bureau of Highways, Administrative Services
Michigan Department of Transportation
E-mail:jagdalev@mdot.state.mi.us
______________________________________________
From Vijay Jagdale:
Date: Tue, 13 Nov 2001 17:49:23 -0500
From: VIJAY JAGDALE
To: ari@pocketdba.com
Subject: Tip 255 more discussion
Hi again,
I got an email from someone on the net on a similar problem. here is the text.
I believe your users could benefit from the solution:
>>> "Krishna Devarajan" 11/04/01 11:33AM >>>
Hi Vijay,
I read your posting here http://www.arikaplan.com/oracle/ari90898.html,
was wondering if you could help me out with my problem...
I have a Search application(java based) that talks to Intermedia
( with a File Datastore). I want to paginate the results and display
them to the User. How do I do that? Say I get 100 results(rows), and
would like to split them into 10 pages with 10 results each. What
would be the best thing to do, query the database for every page? or
return all results in one chunk and split them into pages?
Thanks in advance!
Krishna
***********************************************************
Hi Krishna,
Pagination of reports is a common question. In Oracle8 we have a great feature
where you can have a query as a table name. We can use it handily to generate
row numbers:
select * from
(select rownum "myrow", a.* from customers a where state='CA'
where myrow between 1 and 100
you can have a next button and add 100 rows(or whatever), so the next page will
have:
select * from
(select rownum "myrow", a.* from customers a where state='CA'
where myrow between 101 and 200
etc...
hope that helps.
Vijay Jagdale
Technical Systems Specialist
Michigan Department of Transportation
jagdalev@mdot.state.mi.us
Back to Ari Kaplan's Home Page