Hello,
I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.
CREATE
VIEW [dbo].[vw_SalesRep_Chaining]AS
SELECT
Top 100 percent Rep_id AS RepId,Rep_cd
AS RepCode,Region
as Region, CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico' WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro' WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other' ELSE '' END AS Sub_Region,RSM
AS RSM, UPPER(Regional_Manager) AS Regional_Manager,Market
,Rep_Manager_id
AS SalesManager, UPPER(MarketManager) AS MarketManager,New_Position
AS NewPosition,Rep_Status
AS RepStatus, UPPER(Rep_Payroll_name) AS PayrollNameFROM
(SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt,Rep_Manager_id
, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind FROM dbo.SalesRep WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR (current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM FROM dbo.SalesRep AS SalesRep_2 WHERE (current_ind = 1)) AS B ON A.Rep_Manager_id = B.Repid INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1 WHERE (current_ind = 1)) AS C ON B.RSM = C.Repid1WHERE
(A.Region IS NOT NULL)Order
by Rep_Id, Rep_CdThis is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd

Cannot create a order by view
Armand du Plessis
Order of rows in any table (both tables and views are considered tables) is not guaranteed. You have to use an order by clause in SELECT statements to get data back in a given order:
A few other threads:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=409367&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=245259&SiteID=1