Order by with case

  • 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:

  • Rather than trying to execute code in the aggregation of results, you'd be better off controlling the flow of your query with an IF EXISTS statement.

  • Try declaring @index as int. From BOL: "A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list."

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I think you can not use variable in order by clause.

    Variables are only allowed when ordering by an expression referencing a column name.

  • You can achieve it with use of dynamic SQL.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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)

  • mssqlsrv (9/6/2012)


    I think you can not use variable in order by clause.

    Variables are only allowed when ordering by an expression referencing a column name.

    Yeah you can. Just tried this and it works:

    declare @index int = 4

    select top 10 * from information_schema.COLUMNS

    order by (case @index when 4 then TABLE_SCHEMA else COLUMN_NAME end)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (9/6/2012)


    mssqlsrv (9/6/2012)


    I think you can not use variable in order by clause.

    Variables are only allowed when ordering by an expression referencing a column name.

    Yeah you can. Just tried this and it works:

    declare @index int = 4

    select top 10 * from information_schema.COLUMNS

    order by (case @index when 4 then TABLE_SCHEMA else COLUMN_NAME end)

    Phil, it is not what OP is asking for.

    In case of index != 4 he wants order by the column index not a name!

    You remember that it is possible to do

    SELECT Col1, Col2, Col3 FROM Table ORDER BY 2

    and results will be sorted by the second column (Col2)...

    That what OP really is asking for. So - dynamic SQL

    ...

    DECLARE @sql NVARCHAR(1000)

    SET @sql = 'SELECT Col1, Col2, Col3, Col4, Col5, ColN FROM Table ORDER BY '

    IF @index = 4

    SET @sql += 'ColN'

    ELSE

    SET @sql += CAST( @index AS VARCHAR)

    EXEC sp_executesql @sql

    -- or just: EXEC (@sql)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Phil Parkin (9/6/2012)


    mssqlsrv (9/6/2012)


    I think you can not use variable in order by clause.

    Variables are only allowed when ordering by an expression referencing a column name.

    Yeah you can. Just tried this and it works:

    declare @index int = 4

    select top 10 * from information_schema.COLUMNS

    order by (case @index when 4 then TABLE_SCHEMA else COLUMN_NAME end)

    Try this, Phil:

    DROP TABLE #Tester

    CREATE TABLE #Tester (col1 CHAR(1), col2 CHAR(1), col3 CHAR(1))

    INSERT INTO #Tester (col1, col2, col3)

    SELECT 'A', 'D', 'B' UNION ALL

    SELECT 'B', 'C', 'A' UNION ALL

    SELECT 'C', 'B', 'D' UNION ALL

    SELECT 'D', 'A', 'C'

    SELECT col1, col2, col3

    FROM #Tester

    ORDER BY col1

    -- Sort operator: col1 Ascending

    SELECT col1, col2, col3

    FROM #Tester

    ORDER BY 2

    -- Sort operator: col2 Ascending

    DECLARE @SortColumn INT

    SET @SortColumn = 3

    SELECT col1, col2, col3

    FROM #Tester

    ORDER BY @SortColumn

    -- The SELECT item identified by the ORDER BY number 1 contains a variable

    -- as part of the expression identifying a column position. Variables are

    -- only allowed when ordering by an expression referencing a column name.

    SELECT col1, col2, col3

    FROM #Tester

    ORDER BY (SELECT @SortColumn)

    -- @SortColumn is evaluated to a constant and optimised out: no sort operator

    DECLARE @Statement VARCHAR(8000)

    SET @Statement =

    'SELECT col1, col2, col3

    FROM #Tester

    ORDER BY '+CAST(@SortColumn AS CHAR(1))

    EXEC(@Statement)

    -- Sort operator: col3 Ascending

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Ah yes, thanks guys.

    Note to self: read more carefully next time 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (9/6/2012)


    Ah yes, thanks guys.

    Note to self: read more carefully next time 🙂

    Heh at least your code works, Phil - even if it doesn't quite match the spec. Mine sucks today.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Using Eugene suggestion (@index is declared int to prevent injection, but might create errors):

    declare @index int = 2

    DECLARE @SQL varchar(MAX)

    IF @index = 4

    select CityInternalID, CityDefaultName, CityCountryID

    from TB_City

    where CityCountryID = 121

    order by CityZipCodes

    ELSE

    BEGIN

    SET @SQL = 'select CityInternalID, CityDefaultName, CityCountryID

    from TB_City

    where CityCountryID = 121

    order by ' + CAST(@index AS varchar(10)))

    EXEC( @SQL)

    END

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (9/6/2012)


    Using Eugene suggestion (@index is declared int to prevent injection, but might create errors):

    ...

    [/code]

    You don't need to worry about injection here, check this:

    declare @index VARCHAR(100) = 'DROP EVERYTHING :-)'

    DECLARE @SQL varchar(MAX)

    IF @index = 4

    PRINT 'OK'

    ELSE

    BEGIN

    PRINT 'NOT OK'

    END

    As you check it against integer (4) , SQL will try implicitly convert your @index to integer and it will fail with run-time conversion error if any crap is placed there....

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • You're right, I missed that point.

    I wasn't aware of your second post when I wrote mine, that's why my solution is so similar.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi All,

    The following did't work

    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

    then I tried this one but still threw the error

    declare @var int = 2

    select ContractTypeInternalID, ContractTypeDefaultName, NULL ParentInternalID from TB_ContractType ct

    inner join TB_Contract co on ct.ContractTypeContractID = co.ContractInternalID

    where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and ContractTypeStatusID = 1 and ContractTypeDefaultName like '%%'

    Order By case @var when 1 then str(ContractTypeInternalID) when 2 then ContractTypeDefaultName when 3 then (select NULL) when 4 then str(ContractTypeSequenceOrder) end

    ASC

    Error was

    Conversion failed when converting the nvarchar value 'Etudiant' to data type int.

    'Etudiant' is an entry in column ContractTypeDefaultName.

    Any ideas....

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply