Advanced Order By - 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)

Advanced Order By

Author

Topic

thecaptainjs
Starting Member

3 Posts

Posted - 2005-06-08 : 22:50:49

I want to order by a certain way.

Right now my quiery orders by a field with values of
0,1,2
It orders it

2
1
0

I want it to order
1
2
0

The field is User_level

I looked at some things, but cant figure this out.
Any Ideas?

thecaptainjs
Starting Member

3 Posts

Posted - 2005-06-08 : 22:52:01

Sorry for double post, subscribing to the topic.

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 23:01:27

well when you do an order by it will sort according to what you've specified, since 0 is < 1 when you do the sorting 0 comes first before 1 and 2, anyway what is the purpose of putting 0 last in the order?

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

thecaptainjs
Starting Member

3 Posts

Posted - 2005-06-08 : 23:14:12

The array is used in a phpbb code.

Currently it goes
2=mod
1=admin
0=Regular user.
Its sorted in reverse order.

But Here is how I want it.
1=admin
2=mod
0=Regular user.

Thats how I want it to display.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-08 : 23:28:59

[code]
SELECT <Whatever>
FROM <Whereever>
ORDER BY CASE User_level WHEN 0 THEN 42 ELSE User_level END ASC
[/code]

DavidM

A front-end is something that tries to violate a back-end.

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-08 : 23:56:41

what does order by 42 do??

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-06-09 : 00:10:28

It's the meaning of life

Damian
"Foolish inconsistency is the hobgoblin of little minds." - Emerson

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-06-09 : 07:52:06

"ORDER BY CASE User_level WHEN 0 THEN 42 ELSE User_level END ASC"

....can be restated as

"ORDER BY CASE User_level WHEN 0 THEN 99999 ELSE User_level END ASC"
where 99999 is a substitute value just for sorting purposes....in this case the user wants the 'low valued' record to be sorted last....so we give it a HIGH value.

Play around with the suggested code!

42 was just a SAMPLE 'high' value!!!....in some instances it wouldn't be 'high' enough.

Why 42?....(humour??)....see also (I believe) "Hitchhikers Guide to the Galaxy"....(Afficionado's...please don't pull me up on any offset with regard to this one!!!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources