locked
Order by behaving differently in SQL Server 2005 vs SQL Server 2014 RRS feed

  • Question

  • Hi

    Can you guys  please help on below query

    we are migrating from sql server 2005 to sql server 2014 .in one stored procure we are having below query

    select distinct Prod_No,Grp_Code,Grp_Desc from #Temp T where Grp_Code in
    (select top 1 Grp_Code from #Temp where T.Prod_No=Prod_No
    order by substring(Grp_Code,2,1) asc)

    and data like below

                         Prod_No Grp_Code Grp_Desc
    1   H4D CMilk
    2   H4D CMilk
    3   H4F YDRINKS
    4    H4F YDRINKS

    order by is behaving differently in 2005 and 2014 .in 2005 we are getting result as 1 H4D CMilk. in 2014 we are getting 3 H4F YDRINKS. but my requirement is we need to get same result like 2005 in 2014 sql server also.

     


    thej1990

    Tuesday, November 20, 2018 2:02 PM

All replies

  • You order the sub-query by the second char of Grp_Code and this is for all "4"; for me an expected & more random result. You have to refine the order clause.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 20, 2018 2:24 PM
  • It should be this way:

    order by substring(Grp_Code,2,1) asc),Prod_No asc

    Tuesday, November 20, 2018 2:31 PM
  • Sorry your sample data is too little to understand the issue and also the query condition doesnt match data you posted to display any issue.

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 20, 2018 2:32 PM
  • You need to have outer query Order by statement,  Try below query

    select distinct T.Prod_No,T.Grp_Code,T.Grp_Desc

    from #Temp T

    where T.Grp_Code in
    (select top 1 z.Grp_Code from #Temp  z where T.Prod_No=z.Prod_No
    order by substring(z.Grp_Code,2,1) asc)

    order by T.Prod_No


    mohammad waheed

    Tuesday, November 20, 2018 4:36 PM
  • Hi Thej1990,

    Please try following script.

    -----drop table #Temp 
    create table #Temp 
    (
    Prod_No int,
    Grp_Code varchar(10),
    Grp_Desc varchar(10),
    )
    insert into #Temp values 
    (1,'H4D','CMilk'),
    (2,'H4D','CMilk'),
    (3,'H4F','YDRINKS'),
    (4,'H4F','YDRINKS')
    
    select top 1 * from #Temp order by substring(Grp_Code,2,1) asc,Prod_No asc
    /*
    Prod_No     Grp_Code   Grp_Desc
    ----------- ---------- ----------
    1           H4D        CMilk
    */
    ;with cte as (
    select *, row_number()over (order by substring(Grp_Code,2,1) asc,Prod_No asc) as rn  from #Temp )
    select Prod_No,Grp_Code,Grp_Desc from cte where rn=1
    /*
    Prod_No     Grp_Code   Grp_Desc
    ----------- ---------- ----------
    1           H4D        CMilk
    */


     

    Hope it can help you .

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Ashin_c Thursday, November 22, 2018 6:41 AM
    Wednesday, November 21, 2018 5:51 AM