Quote:
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
Quote:
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?