CODEWITHSUNDEEP
sql
M.JAY
M.JAY

Reputation: 193

sql: order by Date

I would like to order the table by date but my query displays an error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

My Query:

    select intervaldate, [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8]
    from 
    (select intervaldate,amount, name from (Select tSystem.Name, IntervalCount.IntervalDate, 
    sum(case when CounterName = 'Prod' then calculationUnits else 0 end) as Amount from IntervalCount, tsystem where 

    IntervalCount.intervaldate between '2017-03-06 00:00:00.000' and '2017-03-08 00:00:00.000' and IntervalCount.systemid =tsystem.id 

    group by tSystem.Name, IntervalCount.Intervaldate
order by IntervalCount.Intervaldate asc
    ) as T3) as T1 
    pivot 
    (sum (amount)
    for name in ([M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8])

    ) as t2

how can I order by IntervalDate?

Upvotes: 0

Views: 81

Answers (1)

Robert
Robert

Reputation: 25763

As message error said you can't use order by in derived tables (in your case). You should replace order by to the end of query as below

    select intervaldate, [M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8]
    from 
    (select intervaldate,amount, name from (Select tSystem.Name, IntervalCount.IntervalDate, 
    sum(case when CounterName = 'Prod' then Units else 0 end) as Amount from IntervalCount, tsystem where 

    IntervalCount.intervaldate between '2017-03-06 00:00:00.000' and '2017-03-08 00:00:00.000' and IntervalCount.systemid =tsystem.id 

    group by tSystem.Name, IntervalCount.Intervaldate
order by IntervalCount.Intervaldate asc
    ) as T3) as T1 
    pivot 
    (sum (amount)
    for name in ([M1], [M2], [M3], [M4], [M5], [M6], [M7], [M8])

    ) as t2
    order by Intervaldate asc

Upvotes: 3

Related Questions