3

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.

Igor Drincic
  • 1,955
  • 4
  • 21
  • 23

6 Answers6

11

Is there a reason that you can't just do

SELECT rownum, a.* 
  FROM (<<your complex query including GROUP BY and ORDER BY>>) a
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
3

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.

Allan
  • 17,141
  • 4
  • 52
  • 69
Carl
  • 5,881
  • 4
  • 25
  • 24
0

Can you use an in-line query? ie

SELECT cols, ROWNUM
FROM   (your query)
cagcowboy
  • 30,012
  • 11
  • 69
  • 93
0

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.

Mark Roddy
  • 27,122
  • 19
  • 67
  • 71
0

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
Osama Al-Maadeed
  • 5,654
  • 5
  • 28
  • 48
0

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
Aleksander Blomskøld
  • 18,374
  • 9
  • 76
  • 82
zbonig
  • 1