Order By in View ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Order By in View ...

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-14 : 09:07:09

Is there a trick to creating a view with an Order By built in? I was expecting this to work, but no dice....

use pubs
go
create view page47
as
select top 100 percent
    au_lname,
    au_fname
from
    authors
where
    au_id like '%2%'
union
select top 100 percent
    au_lname,
    au_fname
from
    authors
where
    au_id like '%3%'
order by
    au_fname
go
select * from page47

Jay White
{0}

dsdeming

479 Posts

Posted - 2002-08-14 : 09:22:07

Sorry, I should have read more thoroughly. You were already trying what I was going to suggest. I guess I should finish my coffe before I start looking at these things.

Edited by - dsdeming on 08/14/2002 09:24:34

Nazim
A custom title

1408 Posts

Posted - 2002-08-14 : 09:24:33

i dont think it should and it would. To retreive the data in a specific order we have to explicity use order by.

Views here are no different from tables. t

jay i think you have to live with again explicity ordering the data.

Try something like this.

use pubs
go
alter view page47
as

select top 200 *
from
(
select top 100 percent
au_lname,
au_fname
from
authors
where
au_id like '%2%'
union
select top 100 percent
au_lname,
au_fname
from
authors
where
au_id like '%3%'
order by
au_fname) s
order by au_fname
go
select * from page47

--Am sure it will have a telling effect on the performance . as you know we cant give a order by in a view without giving top :(.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 09:52:25

Another thing to try is UNION ALL instead of UNION, and if you need it to remove duplicates then use a SELECT DISTINCT on the outside query.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-14 : 10:23:31

select top 100% * from
(select
au_lname,
au_fname
from
authors
where
au_id like '%2%'
union
select
au_lname,
au_fname
from
authors
where
au_id like '%3%'
) as a
order by
au_fname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources