Oracle ROWNUM

Question

I have a complex query with group by and order by clause and I need a sorted row number (1...2...(n-1)...n) returned with every row. Using a ROWNUM (value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation) gives me a non-sorted list (4...567...123...45...). I cannot use application for counting and assigning numbers to each row.

Answer 1

Is there a reason that you can't just do

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

Answer 2

You could do it as a subquery, so have:

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

That would probably work... don't know if there is anything better than that.

Answer 3

Can you use an in-line query? ie

SELECT cols, ROWNUM
FROM   (your query)

Answer 4

Assuming that you're query is already ordered in the manner you desire and you just want a number to indicate what row in the order it is:

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

and replace "Colx" with the names of the columns 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 anything more than limit the total number of rows returned in a query (e.g. AND ROWNUM < 10) you'll need to alias ROWNUM:

select * (select rownum rn, a.* from () a)) where rn between 500 and 1000