CODEWITHSUNDEEP
oracle-databaserownum
Igor Drincic
Igor Drincic

Reputation: 1985

Oracle ROWNUM pseudocolumn

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.

Upvotes: 3

Views: 4385

Answers (6)

Carl
Carl

Reputation: 5991

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.

Upvotes: 3

zbonig
zbonig

Reputation: 1

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

Upvotes: 0

Osama Al-Maadeed
Osama Al-Maadeed

Reputation: 5695

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

Upvotes: 0

Mark Roddy
Mark Roddy

Reputation: 27936

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.

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30848

Can you use an in-line query? ie

SELECT cols, ROWNUM
FROM   (your query)

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Is there a reason that you can't just do

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

Upvotes: 11

Related Questions