Using DISTINCT on a single column of a multi-column select gets correct results but can get it to order by createon date?

Question

Please review the following query and see if you can help me out.

DECLARE @Contacts TABLE (
  workgroupid  UNIQUEIDENTIFIER,
  contactid    UNIQUEIDENTIFIER,
  leadname     NVARCHAR(130),
  createdon    DATETIME,
  Marketsource NVARCHAR(150))

INSERT INTO @Contacts
            (workgroupid,
             contactid,
             leadname,
             createdon,
             Marketsource)
SELECT workgroupid,
       contactid,
       Coalesce(FirstName + ' ', ' ') + Coalesce(LastName, ' '),
       createdon,
       Marketsource
FROM   Contacts
WHERE  iLeadPartnerID IS NOT NULL
       AND ( CreatedOn BETWEEN @startDate AND @endDate )
       AND MarketSource IS NOT NULL
       AND WorkgroupId = @workgroupID
       AND Len(MarketSource) > 0

DECLARE @tasks TABLE (
  targetid    UNIQUEIDENTIFIER,
  workflow    NVARCHAR(100),
  result      NVARCHAR(50),
  tasktype    NVARCHAR(30),
  completedon DATETIME,
  userid      UNIQUEIDENTIFIER,
  producer    NVARCHAR(50))

INSERT INTO @tasks
            (targetid,
             workflow,
             result,
             tasktype,
             completedon,
             userid,
             producer)
SELECT t.TargetID,
       t.Workflow_ResultChosen_Label,
       t.Result,
       t.TaskType,
       t.Completed,
       t.UserID,
       Coalesce(u.FirstName + ' ', 'Not') + Coalesce(u.LastName, ' Assigned')
FROM   Tasks t
       LEFT JOIN Users u
         ON u.UserID = t.UserID
WHERE  t.CreatedOn BETWEEN @startDate AND @endDate
       AND t.TargetID IN (SELECT contactid
                          FROM   @Contacts)
       AND t.TaskType <> '[=] Auto Email'
GROUP  BY t.TargetID,
          t.UserID,
          t.Workflow_ResultChosen_Label,
          t.Result,
          t.TaskType,
          t.Completed,
          u.FirstName,
          u.LastName

SELECT DISTINCT ( c.Marketsource )                                 AS [Market Source],
                t.producer                                         AS [Producer],
                C.leadname                                         AS [Lead Name],
                t.TaskType                                         AS [Task],
                Coalesce (t.workflow, t.result, ' ')               AS [Task Result],
                Dateadd(HOUR, @utcoffset, c.createdon)             AS [Received On],
                Dateadd(HOUR, @utcoffset, ( MIN (t.completedon) )) AS [Completed],
                (SELECT COUNT(*)
                 FROM   Quotes q WITH (NOLOCK)
                 WHERE  SaleID IS NULL
                        AND q.ContactID = c.contactid)             AS [Pending Quotes],
                (SELECT COUNT(*)
                 FROM   Sales s WITH (NOLOCK)
                 WHERE  s.Client_ID = c.contactid)                 AS [Total Sales]
FROM   @Contacts c
       INNER JOIN @tasks t
         ON t.targetid = c.contactid
GROUP  BY c.Marketsource,
          c.createdon,
          c.contactid,
          c.leadname,
          t.producer,
          t.tasktype,
          t.workflow,
          t.result,
          t.completedon 

Answer 1

Well yes - sure - just add an ORDER BY c.CreatedOn to your last query - and since you're getting that error, add the c.CreatedOn column to your list of fields being selected:

SELECT DISTINCT 
   c.Marketsource AS [Market Source],
   c.CreatedOn,
   t.producer AS [Producer],
   c.leadname AS [Lead Name],
   t.TaskType AS [Task],
   COALESCE(t.workflow, t.result, ' ') AS [Task Result],
   DATEADD(HOUR, @utcoffset, c.createdon) AS [Received On],
   DATEADD(HOUR, @utcoffset, MIN(t.completedon)) AS [Completed],
   (SELECT COUNT(*)
      FROM dbo.Quotes q WITH (NOLOCK)
      WHERE SaleID IS NULL
        AND q.ContactID = c.contactid) AS [Pending Quotes],
   (SELECT COUNT(*)
      FROM dbo.Sales s WITH (NOLOCK)
      WHERE s.Client_ID = c.contactid) AS [Total Sales]
FROM   
   @Contacts c
INNER JOIN 
   @tasks t ON t.targetid = c.contactid
GROUP BY 
   c.Marketsource, c.CreatedOn, c.contactid, c.leadname,
   t.producer, t.tasktype, t.workflow, t.result, t.completedon 
ORDER BY 
   c.CreatedOn