SQL error with order by clause

Question

I am getting 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

for below code,

SELECT      MIDDLE.MODEL AS Model,  
                    MIDDLE.MANUFACTURER AS Manufacturer,
                    {fn CONCAT(CAST(AVG(ABS(MIDDLE.AVG_YEAR)/365) as Decimal(10, 2)), ' Years')} AS AverageAge, 
                    {fn CONCAT('$',CAST(SUM(MIDDLE.ACQCOST) as Decimal(15,2)))} AS TotalAcquistionCost, 
                    {fn CONCAT('$',CAST(AVG(MIDDLE.ACQCOST) as Decimal(15,2)))} AS AverageAcquistionCost,  
                    {fn CONCAT('$',CAST(SUM(MIDDLE.SUMofSPENT) as Decimal(15,2)))} AS TotalRepairCost, 
                    {fn CONCAT('$',CAST(AVG(MIDDLE.SUMofSPENT) as Decimal(15,2)))} AS AverageRepairCost 
        FROM 

            (SELECT   
                    PRE.Model AS MODEL, 
                    PRE.Manufacturer AS MANUFACTURER, 
                    PRE.Avg_Year AS AVG_YEAR, 
                    PRE.AcqCost AS ACQCOST, 
                    SUM(PRE.Spent) AS SUMofSPENT FROM 
             (SELECT  

                    E.sn AS SerialNumber, 
                    E.mdl AS Model, 
                    DATEDIFF(year, E.acq, GETDATE()) AS Avg_Year, 
                    E.cost AS AcqCost,  
                    E.mfr AS Manufacturer, 
                    FR.spent AS Spent 
                    FROM rme_failrepair AS FR 


            INNER JOIN rme_endo AS E 
                    ON FR.acq_id = E.acq_id 
                    AND FR.en_sn = E.en_sn
            WHERE E.mdl = @mdl

                     ORDER BY SerialNumber, Spent )AS PRE 
                     GROUP BY PRE.SerialNumber) AS MIDDLE

Just remove this `ORDER BY SerialNumber, Spent` no use of using it inside `Sub-Query` — Pரதீப், May 02 '16 at 17:54
The error is self-explanatory. Remove the `ORDER BY` clause - ordering a sub-query wouldn't impact the order of the result anyway. — Siyual, May 02 '16 at 17:55
It will affect the group by clause then. Should I remove that as well? — Nilanjan Joshi, May 02 '16 at 17:59

score 0 · Answer 1 · answered May 02 '16 at 18:05

As mentioned in the comments, this should be the query. Also note that your GROUP BY clause is wrong too. You have include many columns in SELECT which do not appear in GROUP BY

 SELECT      
        MIDDLE.MODEL AS Model,  
        MIDDLE.MANUFACTURER AS Manufacturer,
        {fn CONCAT(CAST(AVG(ABS(MIDDLE.AVG_YEAR)/365) as Decimal(10, 2)), ' Years')} AS AverageAge, 
        {fn CONCAT('$',CAST(SUM(MIDDLE.ACQCOST) as Decimal(15,2)))} AS TotalAcquistionCost, 
        {fn CONCAT('$',CAST(AVG(MIDDLE.ACQCOST) as Decimal(15,2)))} AS AverageAcquistionCost,  
        {fn CONCAT('$',CAST(SUM(MIDDLE.SUMofSPENT) as Decimal(15,2)))} AS TotalRepairCost, 
        {fn CONCAT('$',CAST(AVG(MIDDLE.SUMofSPENT) as Decimal(15,2)))} AS AverageRepairCost 
FROM 
(
    SELECT  
        E.sn AS SerialNumber
        E.mdl AS Model, 
        DATEDIFF(year, E.acq, GETDATE()) AS Avg_Year, 
        E.cost AS AcqCost,  
        E.mfr AS Manufacturer, 
        SUM(FR.Spent) AS SUMofSPENT
    FROM rme_failrepair AS FR INNER JOIN rme_endo AS E 
        ON FR.acq_id = E.acq_id AND FR.en_sn = E.en_sn
    WHERE E.mdl = @mdl
    GROUP BY 
        E.sn, E.mdl, DATEDIFF(year, E.acq, GETDATE()), E.cost, E.mfr
) AS MIDDLE

FLICKER · Answer 2 · 2016-05-03T14:54:59.880

0

If you need ORDER BY in view or derived table, you need to specify PERCENT

(SELECT  TOP 100 PERCENT 

                E.sn AS SerialNumber, 
                E.mdl AS Model, .....

edited May 03 '16 at 14:54

answered May 02 '16 at 18:07

FLICKER

6,439
4
45
75

I tried it but did not work. It shows the same error – Nilanjan Joshi May 03 '16 at 14:44
Maybe I put the top percent in wrong place. The idea is having that for the SELECT that you have ORDER BY for. You need that for the sub-query with alias PRE – FLICKER May 03 '16 at 14:51

SQL error with order by clause

2 Answers2