does anyone know how to return multiple rowsets from an oracle stored procedure.

does anyone know how to limit the number of rows that are returned from a select query in oracle. I have a query that returns 100,000 rows. I only want to return the first 10 rows.

    Not sure specifically about Oracle, but most select statements will take a LIMIT parameter to restrict the number of rows returned

    select * from table limit 10

    some will also allow for offsetting so you could start at the 11th row on the next statement. Check your local select syntax for the exact statement, or just give it a try 🙂

    Tim Frank

      4 months later

      A couple of different possibilities, depending on the database product you are using.

      Below are several examples, in addition to the LIMIT clause that Tim suggested. (Oracle doesn't support LIMIT, though.)

      All examples show a query that returns the top 10 largest orders.

      ====

      Several DB vendors support the "SET ROWCOUNT {number}" statement. You can use this before your query statement to have any query stop after the first XX rows. You'd use it like so:

      SET ROWCOUNT 10
      SELECT Salesperson, Company, Total
      FROM Orders
      WHERE State = 'TX'
      ORDER BY Total DESC

      (Remember to use an ORDER BY clause, or you will get unpredictable results.)

      ====

      Sybase SQL Server, Sybase SQL Anywhere, Microsoft Access, and Microsoft SQL Server support the following syntax:

      SELECT TOP {n} [PERCENT] [WITH TIES]
      {column list}
      FROM {table};

      (The WITH TIES hint forces tied values to be counted as one row. Some other products use the "FIRST" keyword instead of TOP.) Here is an example:

      SELECT TOP 10 Salesperson, Company, Total
      FROM Orders
      WHERE State = 'TX'
      ORDER BY Total DESC

      (Again, remember to use an ORDER BY clause.)

      ====

      Oracle (and some other vendors) support "rownumber" that is a sequential number in a system-generated column.

      Since Oracle 8i supports correlated inner queries with an ORDER BY clause, you can write your query this way:

      SELECT Salesperson, Company, Total
      FROM (SELECT *
      FROM Orders
      ORDER BY Total DESC)
      WHERE ROWNUM < 10;

      Prior releases of Oracle did not support ORDER BY in nested queries, so you will have to use this work-around:

      SELECT Salesperson, Company, Total
      FROM Orders A
      WHERE 10 >= (SELECT COUNT(DISTINCT Total)
      FROM Orders B
      WHERE B.Total >= A.Total)
      ORDER BY Total DESC;

      For more information, check out the Oracle SQL FAQ at:
      http://www.orafaq.org/faqsql.htm

      ====

      Finally, the best approach is one that does not rely on vendor-specific syntax, so it is portable across most (if not all) database products.

      SQL is a set language, so you can use a self-join to combine two copies of the same table, then grab the outer-most (maximum) value three times, like so:

      SELECT Elements.Salesperson,
    Elements.Company,
    Elements.Total
FROM Orders AS Elements, 
    Orders AS Boundary
WHERE Elements.Total <=
    Boundary.Total
GROUP BY Elements.Salesperson, 
    Elements.Company, 
    Elements.Total
HAVING COUNT(DISTINCT Boundary.Total) 
    <= 3;

      This will return tied values. To exclude them, simple remove the "DISTINCT" predicate.

      For more information, see Joe Celko's column at:

      http://www.dbmsmag.com/9610d06.html

      Enjoy!

      • Dan
        9 months later

        Hi Tim:

        If you have the complete query and just want to limit the result to 10 (for example), you only need to add this line:

        and rownum<=10

        Thats all and its very usefull.

        Saludos de Mexico!!!

          2 years later

          I am hitting this problem and this is the only post I've found on the subject. I'm running a sybase database (well, work makes me).

          I've tried the TOP 10 and it does not work in my SQL sytax.

          I was wondering how, with PHP, to set the rowcount. Does that go inside the SQL query, or into a PHP statement?

          SET ROWCOUNT 10
          SELECT Salesperson, Company, Total
          FROM Orders
          WHERE State = 'TX'
          ORDER BY Total DESC

          How would that work with PHP?
          This is my query:

          $sqlsearch="select * from ETECHLOG where 
(( convert(float,ENTERED_DATE) 
between $new_fromdate and $new_todate ) AND
 (ENTERED_BY like '$user') AND (SYSTEM like '$system') AND
 (upper(COMPONENT) like upper('%$component%')) AND 
(upper(SHORT_DESC) like upper('%$shortdesc%')) AND 
(upper(SHORT_RES) like upper('%$shortres%')) AND 
(upper(LONG_DESC) like upper('%$longdesc%')) AND 
(upper(LONG_RES) like upper('%$longres%'))) 
order by ENTERED_DATE DESC";
$rowresults = sybase_query($sqlsearch, $linkID);

          Edit: I am wanting to put the Next Page/Previous Page Links on my search results page... maybe there is another way to do this, though...

            Write a Reply...