Hi, I have a function that returns a string of records returned by a subquery. This has worked fine in the past, but all of a sudden the subquery started returning items not in the order they are being inserted. And it won't let me add an order by clause to the subquery either. If I add select top 100, it works, and this will never return more than like 20, but I don't know if it's a bad idea...ALTER FUNCTION [dbo].[udf_mp_getShotsForTeamHole] ( -- Add the parameters for the function here @matchid int, @sideid int, @holeid int)RETURNS varchar(64)ASBEGIN -- Declare the return variable here DECLARE @shots varchar(64) -- Add the T-SQL statements to compute the return value here SELECT @shots = (COALESCE(@shots + ', ', '') + abbr) FROM ( SELECT ms.hole_id, ms.shot_number, ISNULL(st.abbr,'U') abbr, st.description FROM match_shots ms LEFT OUTER JOIN shot_types st ON st.id = ms.shot_type_id WHERE ms.match_id = @matchid AND ms.side_id = @sideid AND ms.hole_id = @holeid ORDER BY ms.shot_number ) shots_data -- Return the result of the function RETURN @shotsEND
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Order by clause invalid in a function
Posted - 2011-08-10 : 15:35:26
