How can I use ORDER BY in a subquery?

Question

My query is:

SET @query=
  N'SELECT TaskID
    ,Company
    ,TaskSource
    ,Urgency
    ,Activity
    ,StatusName
    ,RequesterMailID
    ,tsk.CreatedDate
    ,tsk.LastUpdatedDate
    ,LastUpdatedBy
    ,(DATEDIFF(DAY, dbo.GetTimeByTimeZoneId(SLAStartTime, '' NZST ''), dbo.GetTimeByTimeZoneId(SLAActualEndTime, '' NZST ''))) 
        + 1 
        - count([Pact_OEMS].[dbo].[MstHolidays](HolidayDate)) AS AgeinDaystilldate
FROM TaskMaster tsk
LEFT OUTER JOIN TrnTaskReportDetails tsr ON tsk.TaskID = tsr.intTaskId
FULL OUTER JOIN TrnSLADetails sla ON tsk.TaskID = sla.intTaskId
FULL OUTER JOIN [Pact_OEMS].[dbo].[MstHolidays] ON [Pact_OEMS].[dbo].[MstHolidays].HolidayId = sla.intTaskId
WHERE CreatedDate >= @FromDate
    AND CreatedDate <= @ToDate
    AND TaskID = @TaskID
ORDER BY tsk.CreatedDate
    ,tsk.LastUpdatedDate'

And the error:

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.

How can I use ORDER BY in a sub query?

Possible duplicate of [The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions](https://stackoverflow.com/questions/18031421/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu) — Bill Tür stands with Ukraine, Sep 14 '17 at 09:55
Does that specific statement in fact produce that error? There's no view, inline function, derived table, subquery or cte in play here as far as I can see. — Lasse V. Karlsen, Sep 15 '17 at 11:15

singhswat · Answer 1 · 2017-09-15T11:12:04.243

Try using window function like below

    SELECT TaskID,
       Company,
       TaskSource,
       Urgency,
       Activity,
       StatusName,
       RequesterMailID,
       tsk.CreatedDate,
       tsk.LastUpdatedDate,
       LastUpdatedBy,
       (DATEDIFF(DAY, dbo.GetTimeByTimeZoneId(SLAStartTime, '' NZST ''), dbo.GetTimeByTimeZoneId(SLAActualEndTime, '' NZST '')))+1-COUNT([Pact_OEMS].[dbo].[MstHolidays](HolidayDate)) AS AgeinDaystilldate,
       ROW_NUMBER() OVER (PARTITION BY TaskID ORDER BY tsk.CreatedDate,
                                                      tsk.LastUpdatedDate) rNO
FROM TaskMaster tsk
     LEFT OUTER JOIN TrnTaskReportDetails tsr ON tsk.TaskID = tsr.intTaskId
     FULL OUTER JOIN TrnSLADetails sla ON tsk.TaskID = sla.intTaskId
     FULL OUTER JOIN [Pact_OEMS].[dbo].[MstHolidays] ON [Pact_OEMS].[dbo].[MstHolidays].HolidayId = sla.intTaskId
WHERE CreatedDate >= @FromDate
      AND CreatedDate <= @ToDate
      AND TaskID = @TaskID

I would also recommend to read this article as it will help you align your thoughts and approach.

Thanks for your response. How should i give group by clause ? — Queries, Sep 14 '17 at 09:58

How can I use ORDER BY in a subquery?

1 Answers1