In the below query, i am selecting Max(Generic Code).
Problem with this query is resulting - Warning Message.
I am aware about row_number() over by Partition
I am not sure about the syntax, Expected result is to avoid duplicate rows and keep the max(Generic Code) row.
from PlacedOrderDetails PC
Left Join Drug Dr
on convert(varchar, Left(PC.NDC ,9),101) = convert(varchar, Left(case when Dr.DrugNDCType = 49 then Dr.DrugNDCNbr else stuff(Dr.DrugNDCNbr,case when Dr.DrugNDCType in (50, 56) then 1 when Dr.DrugNDCType in (51, 57) then 6 when Dr.DrugNDCType = 52 then 10 end, 0, '0') end ,9),101)
Group by PC.ID, PC.[Generic Name], PC.[NDC]
Order by PC.ID ASC