sql: order by Date

Question

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?

Which DBMS are you using?

user330315
– user330315

2017-03-08 13:46:27 +00:00
Commented Mar 8, 2017 at 13:46 — user330315
– user330315, Commented Mar 8, 2017 at 13:46

M.JAY · Accepted Answer · 2017-03-10 08:47:32Z

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

Collectives™ on Stack Overflow

sql: order by Date

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related