UDF "order by" comes out in the opposite order desired - SQL 2005
I have a UDF that I had to do the order by desc to get it to be asc and vice-versa. Has anyone else had this problem? or is this a "feature"? Here is my code: ALTER FUNCTION [dbo].[fn_Get_Acq_Cost_By_GPI] (@cGPI char(14), @dDateSubmitted datetime) RETURNS float BEGIN declare @ACQ_Cost float declare @dDateSub datetime select @dDateSub = isnull(@dDateSubmitted, getdate() ) select @ACQ_Cost = receive_unit_price from drugpricing.dbo.VendorPO inner join pricing.dbo.mddb_GPI on drugpricing.dbo.VendorPO.ndc = Pricing.dbo.MDDB_GPI.ndc where Pricing.dbo.MDDB_GPI.GenericProductIdentifier = @cGPI and @dDateSub >= datestamp and receive_unit_price is not null order by drugpricing.dbo.VendorPO.datestamp, receive_Unit_Price desc select @Acq_Cost = isNull(@ACQ_Cost, 99999) -- added 6/24/10 msk return @ACQ_Cost END ------------------------------------------------- From this UDF, I want the record with the newest date and the lowest price for that date. If I do it with: order by drugpricing.dbo.VendorPO.datestamp desc , receive_Unit_Price I do not get the right price. I get the oldest date and the highest price. Thanks.
You are going to laugh at this one. Here is the source of your "feature": You have a select statement which returns multiple records, but then you use a variable assignment which is executed as many times as there are records in your select. By the time it bails out, it gets the value which belongs to the last record of your select which is of course the opposite from what you want because you really need it to be equal to the value from the first record not the last. All you need to do is limit your select such that it returns only one record. This way you don't have to flip your order by clause to the opposite and also you will have your function return desired result much faster. To make the long story short, make your select look like this: select @ACQ_Cost = t.receive_unit_price from ( select top 1 receive_unit_price from drugpricing.dbo.VendorPO inner join pricing.dbo.mddb_GPI on drugpricing.dbo.VendorPO.ndc = Pricing.dbo.MDDB_GPI.ndc where Pricing.dbo.MDDB_GPI.GenericProductIdentifier = @cGPI and @dDateSub >= datestamp and receive_unit_price is not null order by drugpricing.dbo.VendorPO.datestamp desc, receive_Unit_Price ) t; select @Acq_Cost = isNull(@ACQ_Cost, 99999); Oleg
I may be overlooking something here, but it looks like you only have to use DESC after the date column rather than after the receive_Unit_Price clumn: order by drugpricing.dbo.VendorPO.datestamp DESC, receive_Unit_Price (Since ASC is the default, there no need to add it after receive_Unit_Price unless it is clearer to the reader.)
hi again! funny thing, when I changed it to be how Oleg suggested, it took 55:57 minutes to update 320,335 records. when I changed it back, it took 3:33 mins!!! wow... needless to say I went back to my original code and will just accept that I have to do the opposite order by that I want... weird!!! Mark, what do you mean by this: In your example, this format would be more concise, but not like the OP's syntax: select @result = (select top 1 number from @t order by number desc) mine was more concise or OP's was? thanks!