Order by with case

• rajawat.niranjan

  Old Hand

  Points: 373

  More actions

  September 5, 2012 at 11:21 pm

  #393114

  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:

• Nesuke

  SSC Enthusiast

  Points: 196

  More actions

  September 6, 2012 at 2:23 am

  #1534292

  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.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 6, 2012 at 3:20 am

  #1534325

  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

• mssqlsrv

  SSCrazy

  Points: 2375

  More actions

  September 6, 2012 at 5:11 am

  #1534382

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

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

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 6, 2012 at 5:13 am

  #1534386

  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]

• Jeff Moden

  SSC Guru

  Points: 1004309

  More actions

  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)
  

• Phil Parkin

  SSC Guru

  Points: 246956

  More actions

  September 6, 2012 at 8:33 am

  #1534511

  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)

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 6, 2012 at 8:41 am

  #1534518

  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]

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 6, 2012 at 8:43 am

  #1534520

  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

• Phil Parkin

  SSC Guru

  Points: 246956

  More actions

  September 6, 2012 at 8:47 am

  #1534523

  Ah yes, thanks guys.

  Note to self: read more carefully next time 🙂

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 6, 2012 at 8:52 am

  #1534532

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 6, 2012 at 9:04 am

  #1534542

  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

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  September 6, 2012 at 9:16 am

  #1534558

  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]

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 6, 2012 at 9:38 am

  #1534577

  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

• rajawat.niranjan

  Old Hand

  Points: 373

  More actions

  September 13, 2012 at 3:01 am

  #1537080

  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....