SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order by with case


Order by with case

Author
Message
rajawat.niranjan
rajawat.niranjan
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 155
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
Nesuke
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 83
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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 19557
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
mssqlsrv
mssqlsrv
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 579
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
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5108 Visits: 5478
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88424 Visits: 41128
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18965 Visits: 20460
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5108 Visits: 5478
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16636 Visits: 19557
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18965 Visits: 20460
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search