I have a query where its update but its doing the inner join with few other tables however the joins returns more than one row so system is taking by default top row and updating that details in main table; where I would like to do the order by based on certain fields before it does the update.
Update A set a.accntid = case(when x.isComp = 'Y' then aa.aacntid else a.id end)
from A inner join PI on a.pid = PI.pid
inner join A aa on a.fid = aa.fid
cross apply (select isComp from dbo.FnXYZ(PI.pid, aa.fid,a.fid) x
The problem here is, if we have one record for given aa.fid then its fine; if the resultset is more than that then its always take the records has isComp = 'N' (assume if we have two records for this result and one record si 'Y' and othe is with 'N' then its always selects 'N' record by default)
I wanted to select Y record first if it exists if no Y record then any 'N' record is fine. not sure where to write order by for this;
Note: i have done this using CTE but looking for other alternatives as CTE sometimes takes hit on performance.