Rownum on a client side

[dananiev]

My users want to have a column that shows row number (mostly for export in MS Excel). ROWNUM on server side does not work with ORDER BY. Is there a way to make it? May be calculated field some how?

 

[benthicsoftware]

I forget what version of Oracle you can do this in (starting with 8.0?) but you can get rownum to work after an 'order by' by making the query an 'inline view' and using rownum on the outside part. Here's an example:

select rownum, t.* from (select * from emp order by ename) t;

Good luck!

Mark Ford
Benthic Software

 

[dananiev]

I have Oracle 8.0.6 and can not use order by in the subquery. IMHO it should be done on client side but I don't know how.

 

[Marco Kalter]

I'm not sure I understand the logic of an "order by rownum" concept. A rownum is not a property of a record in a table, but the sequence number of a record in a result set. The rownum of each record in a result set depends on the order by clause and where clause that you are using.

What should the order be when you order by rownum?

------------------
Marco Kalter
Allround Automations

 

[dananiev]

All
I solved it. I created Calculated field RN and in OnCalculate()

qexportRN->AsInteger=qexport->RecNo;

 

[benthicsoftware]

Sounds like the order by in a subquery must have been added in 8.1. Marco, the rownum gets assigned before the order by statement is processed in Oracle. So it is quite easy to do a query where the rownum comes back out of order (boy, was I surprised the first time I saw that!) They fixed it by allowing order by in an inline view (which works very well.)

-Mark Ford