This is a kind of followup thread to a thread "Select statement" started by hilluly yesterday. That thread has unfortunatelly allready been closed, so I can't post my thoughts there, so I'm starting a new one.

What provoked me was the following:

Originally posted by chrisrlong
While I applaud the general approach of the solution, I would argue against its usage of ROWID. IMHO, ROWID should never be used in production SQL. I might suggest the following instead:

select contract_number,col_1,col_2,col_3,...,col_n
from the_table a
where CCode || ' ' || DCode || ' ' || LocationCode
=(select min(CCode || ' ' || DCode || ' ' || LocationCode )
from the_table
where contract_number=a.contract_number);
I can't agree with Chris here. Why shouldn't ROWID be used in a SQL? Yes, ROWIDs should not be stored (with some rare exceptions) in a tables for later use, and they should not be used in PL/SQL programs, but I can see no reason why they should not be used inside SQL statements, where read consystency is assured. Chris, I can see no threat in using ROWID like in svk suggested:

Originally posted by svk

select contract_number,col_1,col_2,col_3,...,col_n
from the_table a
where rowid=(select min(rowid)
from the_table
where contract_number=a.contract_number);
Why would you advice against such usage of ROWID? Besides, your variation of this query is not only slower and less readable, it could also be wrong. What if there is more than one row that equals the expression MIN(CCode || ' ' || DCode || ' ' || LocationCode)? Your query would return ORA error instead of the corect result. Unless of course you assumed that those three columns constitute the composite PK?

In any case, I think usage of ROWID in selects and DMLs is perfectly OK. Any other thoughts about that?