title: SQL Function, COALESCE & Sorting
body: <pre><code>ALTER FUNCTION [dbo].[fnGetTrustNotesByTrustIDForAdminConsole] 
  ( @TrustID INT
  )
  RETURNS varchar(MAX)
  AS
  BEGIN
      DECLARE @listStr VARCHAR(MAX)
  
      SELECT @listStr = COALESCE(@listStr + '|' , '') + ltrim(Note.NoteText)
      FROM   TrustNote
      JOIN   Note 
      ON     TrustNote.NoteID = Note.NoteID
      and    Note.Archived = 0
      WHERE  Trustid = @TrustID
  
      RETURN @listStr
  
  END
  </code></pre>
  
  <p>This function returns "Notes" from the database and compiles them, however there is a  another column in Note (Note.ListOrderNumber) that I would like to sort by in addition to coalesce.</p>
  
  <p>I know it doesn't work to just add in an order by prior to returning, but is there a way to add this in?</p>
  
  <h1>EXAMPLE DATA</h1>
  
  <pre><code>Note 1, Position 2
  Note 2, Position 3
  Note 3, Position 1
  </code></pre>
  
  <p>Currently retrieves exactly as stated there.
  Adding <code>ORDER BY Note.ListOrderNumber</code> after the Where clause only produces a single result:</p>
  
  <pre><code>Note 2, Position 3
  </code></pre>