How can I set "order by value"

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)

How can I set "order by value"

Author

Topic

mydogdating
Starting Member

5 Posts

Posted - 2006-10-12 : 15:19:27

Hi

I have a column "Listing", and its value is Basic, Feature or Standard.
Now, I am trying to show listing "Feature > Standard > Basic" in order.
As you can see, I can't use "order by listing desc/asc" for my purpose. Anyone knows how to write query in this case???

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-12 : 15:25:21

Use a case statement in your orderby clause.

declare @t table (c varchar(10))
insert into @t
select 'Feature' as c union select 'Standard' union select 'Basic'
select c
from @t
order by
case c
when 'Feature' then 1
when 'Standard' then 2
when 'Basic' then 3
end

There are some tricks to this when some datatype get involved. I suggest you read some of the articles posted here on the subject.

Jay White

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-12 : 15:26:02

[code]
SELECT YourOtherColumns
FROM
(
SELECT
SortOrder =
CASE
WHEN Listing = 'Feature' THEN 1
WHEN Listing = 'Standard' THEN 2
ELSE 3
END,
YourOTherColumns
FROM YourTable
) t
ORDER BY SortOrder
[/code]

Tara Kizer

mydogdating
Starting Member

5 Posts

Posted - 2006-10-12 : 17:28:43

Working great! Thank you so much!!!

quote:

Originally posted by tkizer


SELECT YourOtherColumns
FROM
(
	SELECT 
		SortOrder =
			CASE 
				WHEN Listing = 'Feature' THEN 1
				WHEN Listing = 'Standard' THEN 2
				ELSE 3
			END,
		YourOTherColumns
	FROM YourTable
) t
ORDER BY SortOrder

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources