Take the 2-minute tour ×

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.

share|improve this question
add comment

6 Answers

up vote 11 down vote accepted

Is there a reason that you can't just do

SELECT rownum, a.* 
  FROM (<<your complex query including GROUP BY and ORDER BY>>) a
share|improve this answer
Well, at least than means that there would be two of us doing it that way, lol! –  Carl Oct 1 '08 at 21:14
Though we did use different aliases :-) –  Justin Cave Oct 1 '08 at 21:20
Thanks to both of you :) –  Igor Drincic Oct 1 '08 at 21:26
@Carl, @Justin Cave - I guess now we will have more people doing this :) oh, one more ugly looking hack in our SQL code :( –  IgorK Jan 13 '11 at 9:57
add comment

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.

share|improve this answer
add comment

Can you use an in-line query? ie

FROM   (your query)
share|improve this answer
add comment

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...
    [Your Original Query Here]

and replace "Colx" with the names of the columns in your query.

share|improve this answer
add comment

I also sometimes do something like:

share|improve this answer
add comment

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 
          (<sorted query>) a))
 where rn between 500 and 1000 
share|improve this answer
add comment

Your Answer


