Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Order by with case Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 11:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 14, 2013 2:07 AM
Points: 21, Visits: 143
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?
Post #1355017
Posted Thursday, September 6, 2012 2:23 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:27 AM
Points: 8, Visits: 75
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.
Post #1355105
Posted Thursday, September 6, 2012 3:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1355146
Posted Thursday, September 6, 2012 5:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 108, Visits: 421
I think you can not use variable in order by clause.
Variables are only allowed when ordering by an expression referencing a column name.
Post #1355212
Posted Thursday, September 6, 2012 5:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1355216
Posted Thursday, September 6, 2012 8:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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?


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1355339
Posted Thursday, September 6, 2012 8:33 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
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)




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1355353
Posted Thursday, September 6, 2012 8:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1355360
Posted Thursday, September 6, 2012 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1355362
Posted Thursday, September 6, 2012 8:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:49 PM
Points: 4,973, Visits: 11,660
Ah yes, thanks guys.

Note to self: read more carefully next time



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1355366
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse