Order by with case

Question

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:

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

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

Nesuke SSC Enthusiast Points: 196 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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: 1004403 More actions · Answer 5

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: 246979 More actions · Answer 6

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

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 · Answer 8

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: 246979 More actions · Answer 9

Ah yes, thanks guys.

Note to self: read more carefully next time 🙂

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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 · Answer 11

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 · Answer 12

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 · Answer 13

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 · Answer 14

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