Re: oracle: get number of rows from a select statement

Hi, I have answered this question five or six times. It is a good candidate to go to some FAQS :-) Please see two articles from oracle dev site below: (I am sorry for the big email) Pedro. ************* First ******************** NUMBERING ROWS AFTER THEY HAVE BEEN SORTED SQL*Plus Jon Kostiner, Tools Group April 1993 ____________________________________________________________________________ ____ SQL*Plus users sometimes want to have rows numbered in a sorted order. This can't be achieved using the ROWNUM pseudocolumn, since the ordering is done after the values for ROWNUM had been assigned to rows as they were selected in random order. This is illustrated in the following example. The first selection shows the unsorted selection of ename and rownum from emp, and the second selection illustrates how ROWNUM is useless as a numbering device for ordered selections because the values of ROWNUM are assigned before the ordering is performed. SQL> select ename, rownum from emp; ENAME ROWNUM ---------- ---------- ALLEN 1 JONES 2 BLAKE 3 CLARK 4 KING 5 ADAMS 6 JAMES 7 FORD 8 SQL> select ename, rownum from emp order by ename; ENAME ROWNUM ---------- ---------- ADAMS 6 ALLEN 1 BLAKE 3 CLARK 4 FORD 8 JAMES 7 JONES 2 KING 5 The following select statement achieves the desired ordering/numbering: SQL> select A.ename, count(*) position 2 from emp A, emp B 3 where A.ename > B.ename 4 or A.ename = B.ename and A.empno >= B.empno 5 group by A.empno, A.ename 6 order by A.ename, A.empno; ENAME POSITION ---------- ---------- ADAMS 1 ALLEN 2 BLAKE 3 CLARK 4 FORD 5 JAMES 6 JONES 7 KING 8 This method works by counting the number of records that a particular record is superior than (or equal to) in alphabetical order of employee name. The empno column acts as a unique key to discern between records in case identical names occur in the table. This would not be an efficient method against tables with large numbers of rows. Another alternative is to create an index on the order column, and include a meaningless where clause to force use of the index. SQL> create index sort on emp (ename); Index created. SQL> select ename, rownum from emp where ename > ' '; ENAME ROWNUM ---------- ---------- ADAMS 1 ALLEN 2 BLAKE 3 CLARK 4 FORD 5 JAMES 6 JONES 7 KING 8 The where clause in the query forces SQL*Plus to use the index created on the ename column, and since the index is used, the rows are returned in ascending order. Since this method depends on the inherent order of the index, it cannot be used to return rows numbered in descending order. The last method presented takes advantage of a feature of the database kernel optimizer. SQL> select rownum, ename 2 from emp , dual 3 where emp.ename = dual.dummy (+); ROWNUM ENAME ---------- ---------- 1 ADAMS 2 ALLEN 3 BLAKE 4 CLARK 5 FORD 6 JAMES 7 JONES 8 KING The optimizer evaluates the outer join in this example by using a sort/merge join, which results in the desired sorted order. ____________________________________________________________________________ ___ *********** Second *************** Optimized "Top-N" Analysis Top-N queries ask for the n largest or smallest values of a column. An example is "What are the top ten best selling products in the U.S.?" Of course, we may also want to ask "What are the 10 worst selling products?" Both largest-values and smallest-values sets are considered Top-N queries. Details Top-N queries use a consistent nested query structure with the elements described below. Subquery to generate the sorted list of data. The subquery includes the ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values, a DESC parameter is needed. Outer Query to limit the number of rows in the final result set. The outer query includes: ROWNUM pseudo-column which assigns a sequential value starting with 1 to each of the rows returned from the subquery. WHERE clause used to specify the n returned rows. The outer WHERE clause must use a "<" or "<=" operator. The high-level structure of these queries is: SELECT column_list ROWNUM FROM (SELECT column_list FROM table ORDER BY Top-N_column) WHERE ROWNUM <= N Examples To illustrate the concepts here, we extend the scenario used in our earlier examples. We will now access the name of the sales representative associated with each sale, stored in the "name" column. and the sales commission earned on every sale. The SQL below returns the top 10 sales representatives ordered by dollar sales, with sample data shown in Table 20-9: select ROWNUM AS Rank, Name, Region, Sales from (select Name, Region, sum(Sales) AS Sales from Sales GROUP BY Name, Region order by sum(Sales) DESC) WHERE ROWNUM <= 10 Table 20-9 Example of Top-10 Query Rank Name Region Sales 1 Jim Smith West 2,321,000 2 Jane Riley South 2,002,000 3 Paul Hernandez South 1,951,000 4 Tammy Dewerr East 1,874,000 5 Lisa Ishiru Central 1,508,000 6 Phil Fabrese East 1,467,000 7 Mary Adams West 1,309,000 8 Linda Garton South 1,211,000 9 Tom Cook North 1,189,000 10 David Wu West 1,043,000 This example can be augmented to show the sales representatives' ranks both for sales and commissions in a single query. We now extend our query to include the sales commission earned on every sale, stored in the "commission" column. The extra information requires another layer of nested subquery. Although interpreting several layers of queries can be challenging, the SQL below has been formatted to clarify the meaning. Below is the SQL needed for our scenario, with the sample results shown in Table 20-10. To understand the query, please step through the code following the number sequence shown at the left edge: 4) SELECT ROWNUM as SalesRank, Name, Region, SalesDollars, CommRank from 2) (SELECT Name, Region, SalesDollars, ROWNUM AS CommRank from 1) ( SELECT Name, Region, sum(Sales) AS SalesDollars, sum(commission) FROM Sales GROUP BY Name, Region ORDER BY sum(Commission) DESC ) 3) ORDER BY Sales DESC ) 5) WHERE ROWNUM <=10 Table 20-10 Example of Top-N query with Ranks on Two Columns SalesRank Name Region SalesDollars CommRank 1 Jim Smith West 2,321,000 1 2 Jane Riley South 2,002,000 3 3 Paul Hernandez South 1,951,000 2 4 Tammy Dewer East 1,874,000 5 5 Lisa Ishiru Central 1,508,000 4 6 Phil Fabrese East 1,467,000 8 7 Mary Adams West 1,309,000 6 8 Linda Garton South 1,211,000 7 9 Tom Cook North 1,189,000 12 10 David Wu West 1,043,000 11 Note that the results in Table 20-10 show how commission ranks are not identical to sales ranks in this data set: some representatives had higher or lower commission rates tied to specific sales.