Create a view with a SQL-select-query

Question

i want to create a view for the select query:

select datepart(yy, watch_date) as jaar, sum(price) as totaleOmzet 
FROM Watchhistory
GROUP BY datepart(yy, watch_date)
ORDER BY jaar

Im using Microsoft SQL server Management Studio

The code to create a view what i made was:

CREATE VIEW vShowingYear (jaar, watch_date, price, totaleOmzet)
AS
select datepart(yy, watch_date) as jaar, sum(price) as totaleOmzet 
FROM Watchhistory
GROUP BY datepart(yy, watch_date)
ORDER BY jaar

But the code gave an ERROR...

Has anybody a idea?

And if you are working with `Microsoft SQL server Management Studio` why didnt you tag SQLServer, and come to that why did you tag MYSQL. TAGS are designed to gather a relevant audience for your question, dont spam them — RiggsFolly, Jan 13 '21 at 11:43
Check this: https://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause — Pinx0, Jan 13 '21 at 11:44
Level 15, State 1, Procedure vShowingYear, Line 6 [Batch Start Line 9] 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. — Remco, Jan 13 '21 at 11:46
@Pinx0 So if i remove the orderby clausule it will work? I can order them then when I select my view right? — Remco, Jan 13 '21 at 11:46
@Charlieface well you can trick it to seem that way. true that in the end it is mostly up to the db engine but... it usually works — Pinx0, Jan 13 '21 at 12:48

score 1 · Accepted Answer · answered Jan 13 '21 at 11:47

Remove the columns definition and the order by and you should be fine:

CREATE VIEW vShowingYear
AS
select datepart(yy, watch_date) as jaar, sum(price) as totaleOmzet 
FROM Watchhistory
GROUP BY datepart(yy, watch_date)

You can then do the ORDER BY over the view.

Create a view with a SQL-select-query

1 Answers1