0

Here is my query that is causing the error stated above. I have a feeling it's one of the order by's but which one?

select cast(max(DateAndTime) - min(DateAndTime) as time) as DownTime
from (select pt.*,
         sum(case when datediff(second, prevdt, DateAndTime) <= 1 then 0 else 1 end) over 
             (order by DateAndTime) as grp
  from (select pt.*, lag(DateAndTime) over (order by DateAndTime) as prevdt
        from IncomingProductTracker pt
        where Line1AFaultStatus = 1 and
              DateAndTime > '2014-04-07 8:00:00' and
              DateAndTime < '2014-04-07 17:00:00'
       ) pt
 ) pt
group by grp
order by 1;
Kpt.Khaos
  • 673
  • 3
  • 14
  • 37
  • 1
    @RacilHilan order by 1 orders by the first column. Ordering by alias is more robust than ordinal though. – Martin Smith Apr 15 '14 at 15:36
  • @MartinSmith Does it? We learn something new everyday. – Racil Hilan Apr 15 '14 at 15:38
  • 1
    Can't you just break the query down to find the exact issue? Try the inner derived table on its own first then add the outer one and so on. – Martin Smith Apr 15 '14 at 15:39
  • Removed order by 1 and it did not change anything. – Kpt.Khaos Apr 15 '14 at 15:39
  • If you don't have a DateAndTime column in your select, Order By DateAndTime will complain. – mika Apr 15 '14 at 15:46
  • @mika is cast(max(DateAndTime) not DateAndTime? – Kpt.Khaos Apr 15 '14 at 15:50
  • You should make it cast(max(DateAndTime) as DateAndTime – mika Apr 15 '14 at 15:51
  • I think the issue is the clause `(order by DateAndTime) as grp` in your query. Read [this](http://stackoverflow.com/questions/10039431/how-can-i-use-sum-over) to see how 'OVER' it is used. – user2989408 Apr 15 '14 at 15:52
  • @mika that is my column name... DateTime is not what I want. Just time. – Kpt.Khaos Apr 15 '14 at 15:52
  • 1
    I [can't reproduce this error](http://sqlfiddle.com/#!6/e1dff9/1), are there any additional bits that you haven't posted? – GarethD Apr 15 '14 at 15:56
  • huhu. Then you must add the DateAndTime column in your select. Oh sorry, I see it is already there (in your pt.*). Oh, but then I read "Try migrating your order by clause into the outermost select statement — the only place where order by makes any sense." in here: http://stackoverflow.com/questions/18085990/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu – mika Apr 15 '14 at 15:57
  • 1
    What is @@version and database compatibility level? – Martin Smith Apr 15 '14 at 15:58

0 Answers0