Home Forums SQL Server 2008 T-SQL (SS2K8) Order by with case RE: Order by with case

  • September 6, 2012 at 8:16 am

    #1534499

    rajawat.niranjan (9/5/2012)

    Hi All

    I want to sort some table data using column index

    The following query works:

    declare @index varchar(10) = 4

    select CityInternalID, CityDefaultName, CityCountryID from TB_City where CityCountryID = 121 order by case @index when 4 then CityZipCodes else @index end asc

    But this doesn't if index is other than 4:

    declare @index varchar(10) = 2

    select CityInternalID, CityDefaultName, CityCountryID from TB_City where CityCountryID = 121 order by case @index when 4 then CityZipCodes else @index end asc

    I want to know if this is possible to sort, if index is 4 then by column name other wise by column index? :doze:

    The code is probably working correctly and the devil is actually in the data. What does the unsorted data look like and what do you want it to look like because you're mixing apples and oranges here. If you want the index to sort properly in the order of the zip codes, then you'll have to make the index look the same as the zip code with some left-padded spaces.

    If you want a coded example, please read the first link in my signature line below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)