Sorting multiple columns in various sort directions in a case statement

Question

Post reply

Sorting multiple columns in various sort directions in a case statement

Barry Couch

Old Hand

Points: 373
More actions
September 27, 2012 at 7:45 am

#145707

Hello all,
I am trying to implement server side processing for jQuery DataTables so I need to be able to sort multiple columns in different directions using a case statement and I am having some difficulty. I would really like to do this without using dynamic SQL.
I know I can sort on multiple columns in the same direction using something like:
CASE @SortColumn WHEN 'NameAsc' THEN LastName + ', ' + FirstName END ASC,
but what if I want the LastName sorted DESC and the FirstName sorted ASC, how do I do that?
Thank in advance for your help.

Viewing 14 posts - 1 through 13 (of 13 total)

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

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

Can you post a more complete statement so it's clearer what you're doing?

Barry Couch Old Hand Points: 373 More actions · Answer 2

Sure. Here is what I have currently which works fine for single columns sorts.

BEGIN

insert into #QualifyingStreets

(

RowNum,

StreetName,

AddFrom,

AddTo,

Maintainer,

LCity,

RCity,

LZipCode,

RZipCode,

StrAltName,

Location,

CntyName,

SegLength

)

select Row_Number() OVER(ORDER BY

CASE @SortColumn WHEN 'StreetNameAsc' THEN StreetName END ASC,

CASE @SortColumn WHEN 'StreetNameDesc' THEN StreetName END DESC,

CASE @SortColumn WHEN 'AddFromAsc' THEN AddFrom END ASC,

CASE @SortColumn WHEN 'AddFromDesc' THEN AddFrom END DESC,

CASE @SortColumn WHEN 'AddToAsc' THEN AddTo END ASC,

CASE @SortColumn WHEN 'AddToDesc' THEN AddTo END DESC,

CASE @SortColumn WHEN 'MaintainerAsc' THEN Maintainer END ASC,

CASE @SortColumn WHEN 'MaintainerDesc' THEN Maintainer END DESC,

CASE @SortColumn WHEN 'LCityAsc' THEN LCity END ASC,

CASE @SortColumn WHEN 'LCityDesc' THEN LCity END DESC,

CASE @SortColumn WHEN 'RCityAsc' THEN RCity END ASC,

CASE @SortColumn WHEN 'RCityDesc' THEN RCity END DESC,

CASE @SortColumn WHEN 'LZipCodeAsc' THEN LZipCode END ASC,

CASE @SortColumn WHEN 'LZipCodeDesc' THEN LZipCode END DESC,

CASE @SortColumn WHEN 'RZipCodeAsc' THEN RZipCode END ASC,

CASE @SortColumn WHEN 'RZipCodeDesc' THEN RZipCode END DESC,

CASE @SortColumn WHEN 'StrAltNameAsc' THEN StrAltName END ASC,

CASE @SortColumn WHEN 'StrAltNameDesc' THEN StrAltName END DESC,

CASE @SortColumn WHEN 'LocationAsc' THEN Location END ASC,

CASE @SortColumn WHEN 'LocationDesc' THEN Location END DESC,

CASE @SortColumn WHEN 'CntyNameAsc' THEN CntyName END ASC,

CASE @SortColumn WHEN 'CntyNameDesc' THEN CntyName END DESC,

CASE @SortColumn WHEN 'SegLengthAsc' THEN SegLength END ASC,

CASE @SortColumn WHEN 'SegLengthDesc' THEN SegLength END DESC,

StreetName ASC)

as RowNum,

StreetName,

AddFrom,

AddTo,

Maintainer,

LCity,

RCity,

LZipCode,

RZipCode,

StrAltName,

Location,

CntyName,

SegLength

from tblGISStreetIndex

where

StreetName like @StreetName + '%'

END

I can get the multi columns with the same sort direction using this:

CASE @SortColumn WHEN 'StreetName' THEN Maintainer + ', ' + StreetName END ASC,

but how could I do something like sorting by Maintainer ASC and StreetName DESC?

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

How about adding 2 rows to the ORDER BY statement? See 'Example'.

--================ TEST DATA ==========================

if object_id('dbo.tblGISStreetIndex') is not null

drop table dbo.tblGISStreetIndex;

create table dbo.tblGISStreetIndex

(

FirstName Varchar(40),

LastName Varchar(40),

StreetName Varchar(40),

AddFrom Varchar(40),

AddTo Varchar(40),

Maintainer Varchar(40),

LCity Varchar(40),

RCity Varchar(40),

LZipCode Varchar(40),

RZipCode Varchar(40),

StrAltName Varchar(40),

Location Varchar(40),

CntyName Varchar(40),

SegLength int

);

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Doug', 'Carroll', 'High Street' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Harry', 'Carroll', 'High Street' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Edgar', 'Carroll', 'High Street' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Edgar', 'Carroll', 'High Road' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Edgar', 'Carroll', 'High Lane' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Bill', 'Carroll', 'High Street' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Alex', 'Arnold', 'High Town' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Charlie', 'Bailey', 'Higher Moors' );

insert dbo.tblGISStreetIndex (FirstName, LastName, StreetName) values ( 'Sam', 'Smith', 'Low Hovels' );

--select * from dbo.tblGISStreetIndex;

--=============== PROCEDURE ==========================

DECLARE @SortColumn Varchar(20);

SET @SortColumn = 'Example';

DECLARE @StreetName Varchar(40);

SET @StreetName = 'High';

BEGIN

-- insert into #QualifyingStreets

-- (

-- RowNum,

-- StreetName,

-- AddFrom,

-- AddTo,

-- Maintainer,

-- LCity,

-- RCity,

-- LZipCode,

-- RZipCode,

-- StrAltName,

-- Location,

-- CntyName,

-- SegLength

--)