Seen that... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Seen that...

Author

Topic

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-11 : 10:20:08

I know we talked about ordering by a case statement, but I've never seen it with multiple coulmns.

Like:

ORDER BY
CASE @sort
WHEN 1 THEN a,b
WHEN 2 THEN b,a
END

(or some syntax like that)

I've tryed and tryed and tryed and ... gave up.

Anybody?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-11 : 10:26:38

The correct syntax is:

CASE
WHEN @sort=1 THEN <expression>
WHEN @sort=2 THEN <another expression>
END,
<more fields>

or for your example:
ORDER BY
CASE WHEN @sort=1 THEN a ELSE b END,
CASE WHEN @sort=2 THEN b ELSE a END

The case statement is just like a normal expression.

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-12 : 07:53:56

That's not quite what I ment...

The aim is to order the records by using multiple columns like:

first order by column a then b then c...

(ORDER BY a,b,c,..)

The trick is to be able to use "n" number of coulmns in a ORDER BY CASE statement.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-12 : 08:14:47

You want to specify the sort order in parameters?

order by
case @sort1 when 'cola' then cola when 'colb' then colb else null end ,
case @sort2 when 'cola' then cola when 'colb' then colb else null end ,
case @sort3 when 'cola' then cola when 'colb' then colb else null end ,

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-11-12 : 09:05:57

No,no,no...

Hmmm...maybe It's just me

OK, final version:

A table with NAME, SURNAME, CITY. This recordset needs to be sorted in different ways, like:

1. sort: name,surname,city
2. sort: city,surname,name
3. sort: surname,city,name

sorting preferably with using CASE please...

It's a silly example, but I hope you cought my drift...!?

motokevin
Starting Member

36 Posts

Posted - 2002-11-12 : 13:55:24

The following will work for what you are doing:

ORDER BY
CASE WHEN @sort=1 THEN name END,
CASE WHEN @sort=1 THEN surname END,
CASE WHEN @sort=1 THEN city END,
CASE WHEN @sort=2 THEN city END,
CASE WHEN @sort=2 THEN surname END,
CASE WHEN @sort=2 THEN name END,
CASE WHEN @sort=3 THEN surname END,
CASE WHEN @sort=3 THEN city END,
CASE WHEN @sort=3 THEN name END

This will also work if name, surname, and city all have the same datatype:

ORDER BY

CASE @sort
WHEN 1 THEN name
WHEN 2 THEN city
WHEN 3 THEN surname
END,

CASE @sort
WHEN 1 THEN surname
WHEN 2 THEN surname
WHEN 3 THEN city
END,

CASE @sort
WHEN 1 THEN city
WHEN 2 THEN name
WHEN 3 THEN name
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources