Re: Limiting the number of records selected.
Bill, Try to find a copy of 'SQL for Smarties' by Joe Celko. Chapter 25 Subsets has example code for the Top N values which I have successfully used in the past to get Next N as well. The final iteration of the code to display top three salaries (taken from the book) is :- select distinct count(*), a.salary from employees as a, employees as b where (a.salary = b.salary) group by a.salary having count(*) =3; (I did have to translate the SQL into the local version :-^) In general what is being done is find the number of rows 'less than' the target row and use that to order and then select the required target rows. Hope that makes sense. --Mark Thornber Bill OConnor wrote: I can do this easily with MySQL but doing it with Oracle has not been that obvious to me. I want to use a subset of the selected rows on a webpage. For instance if the query returns 100 rows I want show just 10 of them on the page, 11-20 on the next etc. Is it possible to specify the range of rows returned from the select as part of the select statement? I think I said that right. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
RE: Limiting the number of records selected.
The select statement below, only works if the top 3 salaries are unique. If two (or more) of the top salaried people have similar salaries - this query breaks down (for the intended result). So, as long as the target column is unique, this is an "great" way to limit selected rows... Thanks... -Original Message- From: Mark Thornber [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 14, 2001 12:34 AM Subject: Re: Limiting the number of records selected. Try to find a copy of 'SQL for Smarties' by Joe Celko. Chapter 25 Subsets has example code for the Top N values which I have successfully used in the past to get Next N as well. The final iteration of the code to display top three salaries (taken from the book) is :- SELECT DISTINCT COUNT(*), a.salary FROM employees AS a, employees AS b WHERE (a.salary = b.salary) GROUP BY a.salary HAVING COUNT(*) =3; (I did have to translate the SQL into the local version :-^) In general what is being done is find the number of rows 'less than' the target row and use that to order and then select the required target rows.
Re: Limiting the number of records selected.
Continuing the ongoing saga... I wrote that the "top-n" queries in
Oracle can be handled by using a nested SELECT with an ORDER BY, then
using ROWNUM in the external WHERE clause. But...
I just tried it on our instance and it doesn't work. We are on
Oracle7 Server Release 7.3.4.5.0. Is this something that only works
in Oracle 8+ ? It doesn't seem to like the ORDER BY clause in the
FROM clause query. If I take ORDER BY dsc out the query runs but
not with the desired results. What gives?
Well, I saw this feature (for doing "top-n" queries) listed as one of
the reasons to migrate to Oracle8i:
http://www.elementkjournals.com/dbm/0003/dbm0031.htm
So I wouldn't be surprised if this were indeed a version 8 feature.
Oh, and here's the 8.1.5 documentation on this feature:
http://oradoc.photo.net/ora81/DOC/server.815/a68003/rollup_c.htm#33316
(This is obviously where the docs I referenced earlier are pointing,
but it seems to be removed from the 8.1.6 documentation set. Uhg.)
| SQLr
| 1 SELECT *
| 2 FROM ( SELECT * FROM codes ORDER BY dsc )
| 3*WHERE ROWNUM = 10
|FROM ( SELECT * FROM codes ORDER BY dsc )
| *
| ERROR at line 2:
| ORA-00907: missing right parenthesis
If I take ORDER BY dsc out the query runs but not with the desired
results:
| SQLr
| 1 SELECT *
| 2 FROM ( SELECT * FROM codes )
| 3*WHERE ROWNUM = 10
What gives?
As I said above, it probably is an Oracle 8 feature.
You can always "brute force" this programmatically; just grab them
all, in order, then display only the ones you're interested in. You
can always finish the handle after you've found those.
If the records are very wide, you might save some time by returning
only an identifying field in the desired sort order; after you grab
the id fields for the relevant range of records, you can then fetch
exactly those using an IN clause or similar. (But I'd guess that the
extra execute would be more costly, unless you have lots of very wide
fields in the SELECTed columns!)
Hm. Here's an arcane way of doing it:
http://www.caribdata.co.uk/additional/new_user.html#top_n
And a variant on the same:
http://www.cstone.net/~phh5j/sqlfaq.html#TOP
(Both were the result for a google.com search for "oracle top-n
query".)
If you have a lot of records, however, I'd expect that asking Oracle
to just give you all of them, in sequence, would be faster. Both of
the above methods appear to be quadratic algorithms, while a simple
ordered query should run in only n-log-n time.
The XSQL servlet has "max-rows" and "skip-rows" values for this
reason:
http://otn.oracle.com/tech/xml/xsql_servlet/htdocs/relnotes.htm#ID2827
But I would guess it's doing exactly as I suggest above -- get the
whole thing, and only show the ones you care about (and quit early, if
possible).
In your case, I'd just do:
| my $sql = "SELECT * FROM codes ORDER BY dsc";
| my $sth = $dbh-prepare($sql);
| $sth-execute();
| my $n = 0;
| while (my $cur = $sth-fetch())
| {
| $n++;
| next if $n $start_row_num;
| last if $n $last_row_num;
| # do stuff with $cur here
| }
| $sth-finish();
Hope this helps,
t.
RE: Limiting the number of records selected.
Bill, I'm sure somebody else will have something more specific than this for your needs, but let me give it a shot with the Syntax of the DBMS I use all the time (Which, also does not have a limit keyword). Do you have a TOP keyword? If so, you can probably do something like: SELECT TOP 10 Column FROM DB..Table WHERE Column NOT IN (SELECT TOP 20 Column FROM DB..Table ORDER BY Column) ORDER BY Column That would give you records #21-30 of the full result set (If you have a TOP keyword). Hopefully something like this will help. Steve Howard -Original Message- From: Bill OConnor [mailto:[EMAIL PROTECTED]] Sent: Friday, March 09, 2001 4:48 PM To: [EMAIL PROTECTED] Subject: Limiting the number of records selected. I can do this easily with MySQL but doing it with Oracle has not been that obvious to me. I want to use a subset of the selected rows on a webpage. For instance if the query returns 100 rows I want show just 10 of them on the page, 11-20 on the next etc. Is it possible to specify the range of rows returned from the select as part of the select statement? I think I said that right. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/
Re: Limiting the number of records selected.
"Bill" == Bill OConnor [EMAIL PROTECTED] writes: Bill I want to use a subset of the selected rows on a webpage. For Bill instance if the query returns 100 rows I want show just 10 of Bill them on the page, 11-20 on the next etc. Look at the discussion of the ROWNUM pseudo-column, in the Oracle SQL reference. t.
