Good morning all!! I wonder if I could get some assistance. Im in the middle of migrating from Oracle to SQL Server, and things have gone pretty smoothly, but I ran across a small query that keeps giving me errors, and I know its something to do with restructuring it in SQL Server, but no matter what i do I cant seem to get it. The query is below and the error I get has something to do with "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"
Any help would be greatly appreciated!!
select * from (select SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from MCA.SPEND_PLAN_HISTORY,
(SELECT MAX(SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE) AS latestdate
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345') LATESTRESULTS
WHERE SPEND_PLAN_HISTORY.PROJ_NO = '12345'
and (SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE < #createodbcdate(TodaysDate -30)#
or SPEND_PLAN_HISTORY.PROJ_COMMENT_DATE <> LATESTRESULTS.latestdate)
order by id) where rownum <= 2
Changing the first line to:
select * from (select TOP 1000 SPEND_PLAN_HISTORY.ID, SPEND_PLAN_HISTORY.COMMENT_CONC from
may work for you....