CODEWITHSUNDEEP
sqlsql-serverview
Hosein Djadidi
Hosein Djadidi

Reputation: 196

Assign ID to each row in Microsoft sql server view

Lets say I have a table like this:

create table MyTable (
 Myname varchar (10) primary key not null

) and a few row of data like:

insert into MyTable values ('john');
insert into MyTable values ('Brad');
insert into MyTable values ('James');
insert into MyTable values ('Anna');
insert into MyTable values ('Eric');
insert into MyTable values ('Hossein');

I want to create a view that assign an ID to each row, I have used the select statement below : 

   select rank() OVER (ORDER BY Myname) as ID, MyTable.Myname 
   from MyTable 
   order by ID

The results is quite acceptable, But the problem come out when I try to create view 

create view myview as 
 select rank() OVER (ORDER BY Myname) as ID, MyTable.Myname 
   from MyTable 
   order by ID

My questions are:

1- how can I create the view from the select statement mentioned above?

2- Is there any alternative way that I can use?

Upvotes: 4

Views: 3768

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

order by is not allowed in a view unless you use top. As per the documentation:

The SELECT clauses in a view definition cannot include the following:

  • An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

So, your statement is fine without the order by:

create view myview as 
   select rank() OVER (ORDER BY Myname) as ID, MyTable.Myname 
   from MyTable ;

Even if you include the order by with a top, the results are not guaranteed in a particular order. You can only guarantee that by using order by the outer query.

Upvotes: 2

Related Questions