Post #29,270
2/21/02 2:15:57 PM
|
Limiting Oracle Return Rows?
Some dialects of SQL have an optional row-limiting clause. For example, SQL-Server can do something like: "SELECT TOP 12 * FROM....." to only return a max of 12 rows. MySQL has a similar LIMIT clause I believe. Does anybody know if/what the Oracle SQL equiv is? It might be in a different spot in the sentence because I don't see anything like it near the beginning in the syntax diagrams I have.
________________
oop.ismad.com
|
Post #29,551
2/24/02 1:01:30 PM
|
ROWNUM pseudo-column
It sounds like you want to use the ROWNUM pseudo-column. Put it in your WHERE clause:
select *
from my_table
where rownum < 100
That should get you the first 99 rows. For more details, please see: [link|http://www-wnt.gsi.de/oragsidoc/doc_817/server.817/a85397/sql_elem.htm#33158|Oracle Docs] I hope this helps. Mike
|
Post #29,643
2/25/02 12:50:05 PM
|
Sheesh, man, you still alive?
It's been what, years...?
Hey, cool to have you back!
Christian R. Conrad
Of course, who am I to point fingers? I'm in the "Information Technology" business, prima facia evidence that there's bats in the bell tower. -- [link|http://z.iwethey.org/forums/render/content/show?contentid=27764|Andrew Grygus]
|
Post #29,649
2/25/02 1:45:18 PM
|
Re: Sheesh, man, you still alive?
Christian,
Thanks for noticing and the welcome back. I'm glad to see that most folks are still around.
I hope everything is going well for you.
Mike
|
Post #29,920
2/26/02 8:56:23 PM
|
Thanks!
________________
oop.ismad.com
|
Post #29,985
2/27/02 12:17:38 PM
|
Caution
Just a quick caution on using that method (though it's the only method that I know to do what you want with Oracle):
When ordering the query, it may not work properly. For example, say there's a thousand rows with data in that you want ordered, but only want the first 10 results:
select * from table where rownum <= 10 order by some_column
This won't have the desired effect, since Oracle will pull the first 10 from the thousands rows and then order those 10. If that's what you want, great. However, if you want the thousand sorted and *then* the first 10 pulled, it won't work. You'll have to do something like this:
select * from ( select * from table where 1=1 order by some_column ) where rownum <= 10
Note that the 1=1 in the inside where clause is a dummy and is there just so there is a where clause. Also note that this may not be as fast as you want, but it will give you what you expect (if that's what you want).
Dan Shellman
|
Post #31,903
3/12/02 6:03:55 PM
|
doesn't work here
select * from (select * from c_item_label where 1=1 order by platform_name) where rownum < 10
*
ORA-00907: missing right parenthesis doesn't seem to like the order by on the inner select. Darrell Spice, Jr.
[link|http://home.houston.rr.com/spiceware/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #32,072
3/13/02 5:13:09 PM
|
Re: doesn't work here
Hmm...
Which version of Oracle are you using? I just tried something identical (obviously using a different table structure) and didn't have a problem (that is, it worked fine). I'm using Oracle 8.1.7 (aka Oracle 8i).
I didn't try it through sqlplus, though. I was using JDBC, but I don't see why that would have a difference.
Dan
|
Post #32,195
3/14/02 2:10:39 PM
|
Version 7.3.4.0.0
SQLWKS> select * from v$version
2>
BANNER
----------------------------------------------------------------
Oracle7 Server Release 7.3.4.0.0 - Production
PL/SQL Release 2.3.4.0.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for 32-bit Windows: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production
5 rows selected.
Darrell Spice, Jr.
[link|http://home.houston.rr.com/spiceware/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #32,234
3/14/02 4:52:41 PM
|
Re: Version 7.3.4.0.0
That's curious, as I even verified that it works in sqlplus (for version 8.1.7). Apparently, their (Oracle's) handling of SQL between versions has changed, unless there's some other setting involved (and I'm no DBA, so I can't go there).
I've no idea of another work around (outside of the obvious, but not trivial, upgrade)...other than one of PL/SQL and a temp table (which is probably more trouble than it's worth).
Dan
|
Post #32,243
3/14/02 5:52:59 PM
|
Wouldn't surprise me
That different versions work slightly differently.
We've got an upgrade already planned, I think ver 9.x but am not sure.
Darrell Spice, Jr.
[link|http://home.houston.rr.com/spiceware/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #32,132
3/14/02 8:24:00 AM
|
Alternatively, on Oracle 7, try "...where (1=1)..."? HTH!
|
Post #32,194
3/14/02 2:10:22 PM
|
no go
Darrell Spice, Jr.
[link|http://home.houston.rr.com/spiceware/|SpiceWare] - We don't do Windows, it's too much of a chore
|
Post #32,439
3/16/02 1:01:21 PM
3/16/02 4:31:57 PM
|
Sorry, shoulda jumped-in with this earlier
The subselect was very limited in Oracle V7--I think there aren't many places you can stick it beyond using [NOT] EXISTS in the WHERE clause. I have done some back-porting from 8i to 7.3.4, and to save time, I'll usually "stack VIEWs" instead.
Sorry I don't have finer detail, but it's too danged easy to work in SQL*Plus and let the SQL parser tell me what will and won't fly and replace accordingly rather than learn the nuances and debug it between my ears.
[Added in edit:] Also, I don't have ready access to a 7.3 back-end or 7.3 docs at the moment--writing this post makes me curious enough to try to map it out.
Reminds me of when we went from Terak machines running a Pascal development environment to Borland Turbo Pascal at school: With the Teraks, compiles would take a minimum of two minutes. Turbo could compile the biggest project we were ever assigned for a lab exercise in a matter of a few seconds. Suddenly, getting it right the first time didn't matter as much.
I tend to avoid subselects anyway because my neanderthal mind struggles with second-guessing the CBO. Also, I do some work on HP-UX sometimes, and there were some seriously weird Oracle optimization problems specific to running 7.3.4 on that platform. I haven't seen it stated outright, but Usenet searches I have run indicate a pattern to me. For Oracle 7.3.4 on HP-UX, I sometimes need to redo an entire VIEW chain by adding optimizer hints on the various feeder VIEWs.
To preempt any flames I deserve for longing for the days of the RBO: "I hereby acknowledge that my mental limitations keep me from appreciating the CBO and learning how to tune SQL accordingly." :-)
Mike Organek
Edited by morganek
March 16, 2002, 04:31:57 PM EST
|
