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:
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.
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:
answered Sep 09 '10 at 03:25 PM
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:
(Since ASC is the default, there no need to add it after receive_Unit_Price unless it is clearer to the reader.)
answered Sep 09 '10 at 03:30 PM
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...
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!
answered Sep 10 '10 at 10:03 AM