The ORDER BY clause is invalid in views Msg 1033, Level 15, State 1, Line 64

Question

SELECT DISTINCT 
    t1.Movie, t1.Genre, t1.Author, t1.[Watch Date] 
FROM
    (SELECT 
         m.[Name] AS Movie, mg.Genre, a.Author, 
         [Watch Date] = CONVERT(VARCHAR, wh.[Watch Date], 107)
     FROM 
         Movie m, [Movie Genres] mg, Author a, [Watch History] wh
     WHERE 
         Genre LIKE '%TV Shows%' 
         AND m.GenreID = mg.Id 
         AND m.AuthorID = a.Id 
         AND m.Id = wh.MovieId
     ORDER BY 
         [Watch Date] DESC) t1 --LINE 64
GROUP BY 
    t1.Movie

I get this error:

Msg 1033, Level 15, State 1, Line 64
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.

I'm trying to select the first data of the table with distinct movie name. So I want to store every FIRST record of every movie

Does this answer your question? [SQL Error with Order By in Subquery](https://stackoverflow.com/questions/985921/sql-error-with-order-by-in-subquery) — Bill Tür stands with Ukraine, Dec 07 '20 at 09:50
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Dec 07 '20 at 09:51
Check out `cross apply` for a solution, explained in [this question/answer](https://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join). — Sander, Dec 07 '20 at 09:56
Presumably a movie can have multiple genres too. Where there are multiple, which one do you want to show? Also, is there any particular reason that you are converting your date to a varchar? — GarethD, Dec 07 '20 at 09:57

score 0 · Answer 1 · edited Dec 10 '20 at 03:13

Try:

;with t1 
as(SELECT 
         m.[Name] AS Movie, mg.Genre, a.Author, 
         [Watch Date] = CONVERT(VARCHAR, wh.[Watch Date], 107)
     FROM 
         Movie m, [Movie Genres] mg, Author a, [Watch History] wh
     WHERE 
         Genre LIKE '%TV Shows%' 
         AND m.GenreID = mg.Id 
         AND m.AuthorID = a.Id 
         AND m.Id = wh.MovieId) 

SELECT DISTINCT 
    t1.Movie, t1.Genre, t1.Author, t1.[Watch Date] 
FROM t1  
GROUP BY t1.Movie
ORDER BY [Watch Date] DESC

When you post a question,please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result. So that we’ll get a right direction and make some test.

The ORDER BY clause is invalid in views Msg 1033, Level 15, State 1, Line 64

1 Answers1