Can we use order by clause when defining a view?

 Posted by Ddd on 3/7/2011 | Category: Sql Server Interview questions | Views: 4604 | Points: 40
Answer:

Yes, we can use order by clause when defining a view.
The condition is we have to include either TOP or FOR XML clauses also in the query

example:
create view gt as select * from emp order by ename 
--Wrong

But
create view gt as select top 5 * from emp order by ename
-- Correct


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Kishork80 on: 3/8/2011 | Points: 10
Aggree...but putting the TOP clause ... will restrict the number of rows while fetching. Better write view without any order inside view and use the order by clause while fetching from view.
Example
create view vw_test
as
select * from auth_agcy -- Here no TOP or ORDER BY clause is used just to make sure thatthe view returns all rows.
Go
select * from vw_test order by agcy_id asc -- Use ORDER BY here as per our requirement liek a table query.

Posted by: Kishork80 on: 3/8/2011 | Points: 10
Aggree...but putting the TOP clause ... will restrict the number of rows while fetching. Better write view without any order inside view and use the order by clause while fetching from view.
Example
create view vw_test
as
select * from auth_agcy -- Here no TOP or ORDER BY clause is used just to make sure thatthe view returns all rows.
Go
select * from vw_test order by agcy_id asc -- Use ORDER BY here as per our requirement like a table query.

Posted by: Laghaterohan on: 3/11/2011 | Points: 10
hmmm sounds logical
Posted by: Ddd on: 3/11/2011 | Points: 10
The question was only whether we can use order by clause in a view or not.
if so, how. No point in discusssing it that long. Kindly post these arguments in the
Forums section
Thanks.
Posted by: Alok_niit on: 12/29/2011 | Points: 10
ya totally agree with Ddd

Login to post response