How do I Sort by Name?

Question

Post reply

How do I Sort by Name?

eamonroche

SSCommitted

Points: 1678
More actions
January 20, 2005 at 5:33 am

#66437

I want to sort by CompanyCategoryType
but.........
one of the CompanyCategoryType is "other" and I want this to appear at the BOTTOM
I have succeed in doing this (see sql code below) but it took me a while to figure out and I was wondering IF THERE IS A BETTER/EASIER WAY ????
Thanks
------------------------------------------
SELECT vcName, vcName as sortorder into #temptable0
FROM dbo.tbl_CompanyCategories
where tbl_CompanyCategories.vcName <> 'other'

SELECT vcName, 'zzzzzzzz' + vcName as sortorder into #temptable1
FROM dbo.tbl_CompanyCategories
where tbl_CompanyCategories.vcName = 'other'

select pk_intCompanyCategoryID, vcName, sortorder from #temptable0
union
select pk_intCompanyCategoryID, vcName, sortorder from #temptable1
order by sortorder

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Doesn't make too much sense, but you'll get the idea:

USE PUBS

GO

SELECT *

FROM authors

ORDER BY

CASE WHEN au_lname ='Green' THEN 'ZZZ_'+au_lname ELSE au_lname END

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

eamonroche SSCommitted Points: 1678 More actions · Answer 2

Thanks Frank. That works fine.

Bye the way......

What if I want to sort reverse and put Mr Green on Top

I tried the below code but not working. It seems the DESC keyword causes a crash

----------------------

USE PUBS

GO

SELECT *

FROM authors

ORDER BY

CASE WHEN au_lname ='Green' THEN 'AAA_'+au_lname DESC

ELSE au_lname DESC

END

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

If you want to sort descending and prefix Ms. Green with the prefix 'AAA', why do you expect her to show up on top?

USE PUBS

GO

SELECT *

FROM authors

ORDER BY

CASE WHEN au_lname ='Green' THEN 'ZZZ_'+au_lname ELSE au_lname END

DESC

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

eamonroche SSCommitted Points: 1678 More actions · Answer 4

ok. stupid me.

I was actually trying to be smart and pick your brains on a different problem I had a few days ago which was something like this (the DESC keyword caused a crash):

select Lastname, Age

from Contacts

order by

case

when @sortorder = 'by_name' then Lastname

else Age DESC

end

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I think another problem will be the datatype of 'age'. I suspect something numeric. And since CASE is trying to cast to the highest precendence among its arguments, this might cause errors. Consider this:

DECLARE @sortorder INT

SET @sortorder = 2

USE PUBS

SELECT *

FROM authors

ORDER BY

CASE @sortorder

WHEN 1 THEN au_lname

WHEN 2 THEN CAST(contract AS CHAR)

ELSE au_fname END

DESC

Without the explicite CAST this statemtent will fail.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

eamonroche SSCommitted Points: 1678 More actions · Answer 6

ok. I take your point about datatype. However even if I was using the same datatype (see below) there seems to be a problem when one of the cases uses DESC and the other one doesn't (????)

DECLARE @sortorder INT

SET @sortorder = 2

USE PUBS

SELECT *

FROM authors

ORDER BY

CASE @sortorder

WHEN 1 THEN au_lname

WHEN 2 THEN city DESC

END

DESC

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Okay, finally I see your point here. I don't think you can mix these both into one. I think you need a second parameter to indicate the sort order like this:

USE PUBS

GO

IF OBJECT_ID('test_me') IS NOT NULL

DROP PROCEDURE test_me

GO

CREATE PROCEDURE test_me

@ORDER_CRITERIA INT,

@ORDER_DIRECTION INT

AS

IF @ORDER_DIRECTION = 1

BEGIN

SELECT

*

FROM

authors

ORDER BY

CASE @ORDER_CRITERIA

WHEN 1 THEN au_lname

WHEN 2 THEN au_fname

END

DESC

END

ELSE

SELECT

au_lname

, au_fname

FROM

authors

ORDER BY

CASE @ORDER_CRITERIA

WHEN 1 THEN au_lname

WHEN 2 THEN au_fname

END

GO

EXEC test_me 2,2

DROP PROCEDURE test_me

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

eamonroche SSCommitted Points: 1678 More actions · Answer 8

thanks.

bit surprising that we need to do all this though.

thanks again for your help

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 9

eamonroche:

This is kind of a roundabout way to do it, but it worked for me.

First, declare a variable table with the columns from the main table that you want in your result set.

Then insert rows into the variable table from the main table, ordered any way you want, but excluding all rows from the main table where the category type is "other".

Then insert rows into the variable table from the main table, ordered the same way, but including only those rows from the main table where the category type is "other".

Then select all rows from the variable table without any ordering.

eamonroche SSCommitted Points: 1678 More actions · Answer 10

eamonroche

SSCommitted

Points: 1678

January 20, 2005 at 9:01 am

#538330

thanks edwin

PhilPacha Hall of Fame Points: 3645 More actions · Answer 11

What about this in the ORDER BY clause...

ORDER BY CASE WHEN tbl_CompanyCategories.vcName = 'other' THEN 1 ELSE 0 END, tbl_CompanyCategories.vcName

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

Yes, that's another method. Did think of this, too. Well, guess it boils down now to a matter of preferences.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]