Create view

How can I create a view from a SELECT statement that consists of ORDER BY
clause.
I tried the following but got error from Query Analyser:
Create View AnyView as
(Select * from Table1 Order By ...)
I cannot have an Order By ?Use something like
Create View AnyView as
(Select Top 100 Percent * from Table1 Order By ...)
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:ujD9hJodDHA.748@.TK2MSFTNGP10.phx.gbl...
> How can I create a view from a SELECT statement that consists of ORDER BY
> clause.
> I tried the following but got error from Query Analyser:
> Create View AnyView as
> (Select * from Table1 Order By ...)
> I cannot have an Order By ?
>|||Thanks.
> Use something like
> Create View AnyView as
> (Select Top 100 Percent * from Table1 Order By ...)
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> http://www.extremeexperts.com
>
> "Alan" <alanpltse@.yahoo.com.au> wrote in message
> news:ujD9hJodDHA.748@.TK2MSFTNGP10.phx.gbl...
> > How can I create a view from a SELECT statement that consists of ORDER
BY
> > clause.
> > I tried the following but got error from Query Analyser:
> >
> > Create View AnyView as
> > (Select * from Table1 Order By ...)
> >
> > I cannot have an Order By ?
> >
> >
>|||Why do you want to do that? A table is not ordered. A view is supposed to look like a table. You are
breaking this very important aspect of the relational model here. Why not quite simply specify the
ORDER BY when you work against the view?
The reason why you can have ORDER BY if you have TOP is when you have a query that is really
dependent on TOP to give correct information, and not to expose a "sorted table".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:ujD9hJodDHA.748@.TK2MSFTNGP10.phx.gbl...
> How can I create a view from a SELECT statement that consists of ORDER BY
> clause.
> I tried the following but got error from Query Analyser:
> Create View AnyView as
> (Select * from Table1 Order By ...)
> I cannot have an Order By ?
>|||The reason I am going to create a view from a table order by one of the
fields is I am doing a course at institute, this is one of the exercise
questions.
The question is :
Create a view from a table called Customer, include the first name, last
name (alphabetical order), phone and fax. The primary key of the Customer
table is an auto increment field.
So the table Customer is not sorted by last name but the insertion order.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:uyNUSupdDHA.4020@.tk2msftngp13.phx.gbl...
> Why do you want to do that? A table is not ordered. A view is supposed to
look like a table. You are
> breaking this very important aspect of the relational model here. Why not
quite simply specify the
> ORDER BY when you work against the view?
> The reason why you can have ORDER BY if you have TOP is when you have a
query that is really
> dependent on TOP to give correct information, and not to expose a "sorted
table".
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver|||> So the table Customer is not sorted by last name but the insertion order.
If this course if about relational databases, then above is incorrect. A table is *not* ordered.
This is one of the most basic parts of the relational theory, and if your lecturer didn't manage to
get that across, I fear you have some problems.
> Create a view from a table called Customer, include the first name, last
> name (alphabetical order), phone and fax. The primary key of the Customer
> table is an auto increment field.
Again, a view is supposed to behave like a table (it is, in the relational lingua, a "named table"),
so it should behave just like a table. The various product's ways to "sort a view" are all
non-relational. See my earlier post for more info.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:ey4Lm4ydDHA.3596@.tk2msftngp13.phx.gbl...
> The reason I am going to create a view from a table order by one of the
> fields is I am doing a course at institute, this is one of the exercise
> questions.
> The question is :
> Create a view from a table called Customer, include the first name, last
> name (alphabetical order), phone and fax. The primary key of the Customer
> table is an auto increment field.
> So the table Customer is not sorted by last name but the insertion order.
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:uyNUSupdDHA.4020@.tk2msftngp13.phx.gbl...
> > Why do you want to do that? A table is not ordered. A view is supposed to
> look like a table. You are
> > breaking this very important aspect of the relational model here. Why not
> quite simply specify the
> > ORDER BY when you work against the view?
> >
> > The reason why you can have ORDER BY if you have TOP is when you have a
> query that is really
> > dependent on TOP to give correct information, and not to expose a "sorted
> table".
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
>|||What the Customer table's primary key is INT IDENTITY, so the records are
not sorted by the name, this is what the table is.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:#g3izE2dDHA.3792@.tk2msftngp13.phx.gbl...
> > So the table Customer is not sorted by last name but the insertion
order.
> If this course if about relational databases, then above is incorrect. A
table is *not* ordered.
> This is one of the most basic parts of the relational theory, and if your
lecturer didn't manage to
> get that across, I fear you have some problems.
>
> > Create a view from a table called Customer, include the first name, last
> > name (alphabetical order), phone and fax. The primary key of the
Customer
> > table is an auto increment field.
> Again, a view is supposed to behave like a table (it is, in the relational
lingua, a "named table"),
> so it should behave just like a table. The various product's ways to "sort
a view" are all
> non-relational. See my earlier post for more info.|||In addition, we cannot make the LastName as PRIMARY KEY so I think the
auto-increment is needed.
"Alan" <alanpltse@.yahoo.com.au> wrote in message
news:#H8FjXBeDHA.2320@.TK2MSFTNGP12.phx.gbl...
> What the Customer table's primary key is INT IDENTITY, so the records are
> not sorted by the name, this is what the table is.
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:#g3izE2dDHA.3792@.tk2msftngp13.phx.gbl...
> > > So the table Customer is not sorted by last name but the insertion
> order.
> >
> > If this course if about relational databases, then above is incorrect. A
> table is *not* ordered.
> > This is one of the most basic parts of the relational theory, and if
your
> lecturer didn't manage to
> > get that across, I fear you have some problems.
> >
> >
> > > Create a view from a table called Customer, include the first name,
last
> > > name (alphabetical order), phone and fax. The primary key of the
> Customer
> > > table is an auto increment field.
> >
> > Again, a view is supposed to behave like a table (it is, in the
relational
> lingua, a "named table"),
> > so it should behave just like a table. The various product's ways to
"sort
> a view" are all
> > non-relational. See my earlier post for more info.
>|||I'm sorry, but I don't understand what these two posts has to do with "ordering" of the table...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:uslgy3LeDHA.1820@.TK2MSFTNGP10.phx.gbl...
> In addition, we cannot make the LastName as PRIMARY KEY so I think the
> auto-increment is needed.
> "Alan" <alanpltse@.yahoo.com.au> wrote in message
> news:#H8FjXBeDHA.2320@.TK2MSFTNGP12.phx.gbl...
> > What the Customer table's primary key is INT IDENTITY, so the records are
> > not sorted by the name, this is what the table is.
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:#g3izE2dDHA.3792@.tk2msftngp13.phx.gbl...
> > > > So the table Customer is not sorted by last name but the insertion
> > order.
> > >
> > > If this course if about relational databases, then above is incorrect. A
> > table is *not* ordered.
> > > This is one of the most basic parts of the relational theory, and if
> your
> > lecturer didn't manage to
> > > get that across, I fear you have some problems.
> > >
> > >
> > > > Create a view from a table called Customer, include the first name,
> last
> > > > name (alphabetical order), phone and fax. The primary key of the
> > Customer
> > > > table is an auto increment field.
> > >
> > > Again, a view is supposed to behave like a table (it is, in the
> relational
> > lingua, a "named table"),
> > > so it should behave just like a table. The various product's ways to
> "sort
> > a view" are all
> > > non-relational. See my earlier post for more info.
> >
> >
>