Order by Issue in SQL Code while using temporary tables.. ?

[santoshsri]

Hi below is a SQL Query written at SQL Server 2000. The result has been ordered by a.Location_Numb er , a.Transaction_D ate, but in output the result is not ordered for eg : I am getting records for locaton no 1,2,3,10,12,13, 14,2,2,2,2,3 .. but it should be like this 1,2,2,2,2,2,3,3 ,10,12,13,14..

Any Suggestion plz .. ?

*************** *************** *************** *************** *************** *************** *

IF OBJECT_ID('temp db.dbo.##tmp_Y_ or_N_translator ') IS NOT NULL
drop table ##tmp_Y_or_N_tr anslator

select 'Y' as Code, '1' as Value into ##tmp_Y_or_N_tr anslator insert into ##tmp_Y_or_N_tr anslator values ('N', '2')

-- temporary table for joins only select * from ##tmp_T_or_F_tr anslator
-- this is used to translate 0 to F and 1 to T

IF OBJECT_ID('temp db.dbo.##tmp_T_ or_F_translator ') IS NOT NULL
drop table ##tmp_T_or_F_tr anslator

select cast(0 as bit) as Code, 'F' as Value into ##tmp_T_or_F_tr anslator
insert into ##tmp_T_or_F_tr anslator values (cast(1 as bit), 'T')

select
UPPER(RTRIM(con vert(varchar(1) , 'L'))) as Record_Type,
UPPER(RTRIM(con vert(varchar(90 ), d.Policy_Number ))) as Policy_Number,
UPPER(RTRIM(con vert(varchar(90 ), d.Insured_Name) )) as Insured_Name,
UPPER(RTRIM(con vert(varchar(90 ), d.Insured_Loc_A ddr1))) as Insured_Loc_Add r1,
UPPER(RTRIM(con vert(varchar(90 ), d.Insured_Loc_A ddr2))) as Insured_Loc_Add r2,
UPPER(RTRIM(con vert(varchar(90 ), d.Insured_Loc_C ity))) as Insured_Loc_Cit y,
UPPER(RTRIM(con vert(varchar(90 ), d.Insured_Loc_S tate))) as Insured_Loc_Sta te,
UPPER(RTRIM(con vert(varchar(10 ), d.Insured_Loc_Z ip))) as Insured_Loc_Zip ,
UPPER(RTRIM(con vert(varchar(10 ), d.Policy_Eff_Da te, 101))) as Policy_Eff_Date ,
UPPER(RTRIM(con vert(varchar(10 ), d.Policy_Exp_Da te, 101))) as Policy_Exp_Date ,
UPPER(RTRIM(con vert(varchar(4) , a.Location_Numb er))) as Location_Number , -- not sequence number as in old LPRS.bas (bad name in old version)
UPPER(RTRIM(con vert(varchar(90 ), SUBSTRING(a.Tra nsaction_Type, 1, 1)))) as Transaction_Typ e,
UPPER(RTRIM(con vert(varchar(90 ), a.Borrower_Addr 1))) as Borrower_Addr1,
UPPER(RTRIM(con vert(varchar(90 ), a.Borrower_City ))) as Borrower_City,
UPPER(RTRIM(con vert(varchar(90 ), a.Borrower_Stat e))) as Borrower_State,
UPPER(RTRIM(con vert(varchar(10 ), a.Borrower_Zip) )) as Borrower_Zip,
UPPER(RTRIM(con vert(varchar(10 ), a.Effective_Dat e, 101))) as Effective_Date,
UPPER(RTRIM(con vert(varchar(10 ), a.Expiration_Da te, 101))) as Expiration_Date ,
UPPER(RTRIM(con vert(varchar(10 ), convert(int,a.P roperty_Premium ) ))) as Property_Premiu m,
UPPER(RTRIM(con vert(varchar(10 ), convert(int,a.G L_Premium) ))) as GL_Premium,
UPPER(RTRIM(con vert(varchar(10 ), convert(int,a.F lood_Premium) ))) as Flood_Premium,
UPPER(RTRIM(con vert(varchar(20 ), a.Mortgage_Valu e))) as Mortgage_Value,
UPPER(RTRIM(con vert(varchar(20 ), c.Limits_FP))) as Limits_FP,
UPPER(RTRIM(con vert(varchar(20 ), c.Limits_REO))) as Limits_REO,
UPPER(RTRIM(con vert(varchar(20 ), c.Limits_Mobile _Home))) as Limits_Mobile_H ome,
UPPER(RTRIM(con vert(varchar(20 ), c.Limits_Flood_ Res))) as Limits_Flood_Re s,
UPPER(RTRIM(con vert(varchar(20 ), c.Limits_Flood_ Com))) as Limits_Flood_Co m,
UPPER(RTRIM(con vert(varchar(20 ), c.Limits_Gen_Li ability))) as Limits_Gen_Liab ility,
UPPER(RTRIM(con vert(varchar(3) , convert(int, a.Location_Type )))) as Location_Type,
UPPER(RTRIM(con vert(varchar(10 ), d.REO_Deposit_P rem))) as REO_Deposit_Pre m,
UPPER(RTRIM(con vert(varchar(10 ), d.Forced_Place_ Deposit_Prem))) as Forced_Place_De posit_Prem,
UPPER(RTRIM(con vert(varchar(10 ), d.Flood_Deposit _Prem))) as Flood_Deposit_P rem,
UPPER(RTRIM(con vert(varchar(3) , d.Forced_Place_ Res_Ded_Amt))) as Forced_Place_Re s_Ded_Amt,
UPPER(RTRIM(con vert(varchar(3) , d.Forced_Place_ Com_Ded_Amt))) as Forced_Place_Co m_Ded_Amt,
UPPER(RTRIM(con vert(varchar(3) , d.REO_Res_Ded_A mt))) as REO_Res_Ded_Amt ,
UPPER(RTRIM(con vert(varchar(3) , d.REO_Com_Ded_A mt))) as REO_Com_Ded_Amt ,
UPPER(RTRIM(con vert(varchar(3) , d.Flood_Ded_Amt ))) as Flood_Ded_Amt,
UPPER(RTRIM(con vert(varchar(3) , d.Mobile_Home_D ed_Amt))) as Mobile_Home_Ded _Amt,
UPPER(RTRIM(con vert(varchar(3) , d.Wind_Ded_Amt) )) as Wind_Ded_Amt,
UPPER(RTRIM(con vert(varchar(2) , h.Value))) as Del_Per_Occ_Agg r_Endrs,
UPPER(RTRIM(con vert(varchar(2) , i.Value))) as Mine_Subsidence _Endrs,
UPPER(RTRIM(con vert(varchar(2) , j.Value))) as Flood_Inc_Cost_ Compliance_Endr s,
UPPER(RTRIM(con vert(varchar(5) , d.Sic_Code))) as Sic_Code,
UPPER(RTRIM(con vert(varchar(3) , d.Peril))) as Peril,
UPPER(RTRIM(sub string(convert( varchar(2), a.Location_Clas s_Code), 2, 1))) as Location_Class_ Code,
UPPER(RTRIM(con vert(varchar(6) , a.Sequence_Numb er))) as Sequence_Number , -- For internal data transformation, see next section
UPPER(RTRIM(con vert(varchar(6) , d.Dividend_Code ))) as Dividend_Code,
UPPER(replicate ('0',3-LEN(convert(var char,d.Commissi on_Pct)))+conve rt(varchar,d.Co mmission_Pct)+r eplicate('0',(6-LEN(convert(var char,d.Commissi on_Pct)))-(3-LEN(convert(var char,d.Commissi on_Pct))))) as

Commission_Pct,
UPPER(RTRIM(con vert(varchar(90 ), d.Agent_Code))) as Agent_Code,
UPPER(RTRIM(con vert(varchar(90 ), d.Company_Numbe r))) as Company_Number,
UPPER(RTRIM(con vert(varchar(10 ), d.Policy_Create _Date, 101))) as Policy_Create_D ate,
UPPER(RTRIM(con vert(varchar(3) , e.Value))) as PropCoverage,
UPPER(RTRIM(con vert(varchar(3) , f.Value))) as GLCoverage,
UPPER(RTRIM(con vert(varchar(3) , g.Value))) as FldCoverage,
UPPER(RTRIM(con vert(varchar(3) , convert(int, a.Flood_Zone))) ) as Flood_Zone

FROM
db_FSFELIP.dbo. tbl_Location_Hi story a
INNER JOIN db_FSFELIP.dbo. tbl_Policy_Limi ts c ON
c.Policy_Number =a.Policy_Numbe r
INNER JOIN db_FSFELIP.dbo. tbl_Policies d ON
d.Policy_Number =a.Policy_Numbe r
INNER JOIN ##tmp_Y_or_N_tr anslator e ON
e.Code=a.Proper ty_Coverage
INNER JOIN ##tmp_Y_or_N_tr anslator f ON
f.Code=a.GL_Cov erage
INNER JOIN ##tmp_Y_or_N_tr anslator g ON
g.Code=a.Flood_ Coverage
INNER JOIN ##tmp_T_or_F_tr anslator h ON
h.Code=d.Del_Pe r_Occ_Aggr_Endr s
INNER JOIN ##tmp_T_or_F_tr anslator i ON
i.Code=d.Mine_S ubsidence_Endrs
INNER JOIN ##tmp_T_or_F_tr anslator j ON
j.Code=d.Flood_ Inc_Cost_Compli ance_Endrs
WHERE
a.Transaction_D ate between '01/01/2002' and '01/01/2005' and a.Policy_number = 'LPR 0001191 00'
and
(c.Limits_Eff_D ate = (select MAX(z.Limits_Ef f_Date) from tbl_Policy_Limi ts z where z.Policy_Number = c.Policy_Number ))
order by a.Location_Numb er , a.Transaction_D ate


*************** *************** *************** *************** ****

[debasisdas]

Please follow posting guidelines and use CODE tags around codes to make your post more readable.

Since I dont have the table with me,nor even I have the structure its very difficult to rectify such a big query .