Oracle ROWNUM Pseudo-Competition

Question

I have a complex query with a group by and order by clause, and I need a sorted number of rows (1 ... 2 ... (n-1) ... n) returned with each row. Using ROWNUM (the value is assigned to the string after passing through the predicate phase of the query, but before the query performs any sorting or aggregation) gives me an unsorted list (4 ... 567 ... 123 ... 45 ...). I cannot use the application to count and assign numbers to each line.

Answer 1

Is there a reason you can't just do

SELECT rownum, a.* 
  FROM (<<your complex query including GROUP BY and ORDER BY>>) a

Answer 2

You can do this as a subquery, so that:

select q.*, rownum from (select... group by etc..) q

This will probably work ... I don't know if there is anything better than this.

Answer 3

?

SELECT cols, ROWNUM
FROM   (your query)

Answer 4

Assuming your query is already ordered according to your desire, and you just want the number to indicate which string in that order:

SELECT ROWNUM AS RowOrderNumber, Col1, Col2,Col3...
FROM (
    [Your Original Query Here]
)

and replace "Colx" with the column names in your query.

Answer 5

I also sometimes do something like:

SELECT * FROM
(SELECT X,Y FROM MY_TABLE WHERE Z=16 ORDER BY MY_DATE DESC)
WHERE ROWNUM=1

Answer 6

If you want to use ROWNUM to do something more than limit the total number of rows returned in the query (e.g. AND ROWNUM <10), you will need to assign the alias ROWNUM:

 select * 
   (select rownum rn, a.* from 
          (<sorted query>) a))
 where rn between 500 and 1000