Using your own sorting routine

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)

Using your own sorting routine

Author

Topic

HauntDog
Starting Member

2 Posts

Posted - 2003-11-07 : 06:57:26

Hi,

I wondered if someone could help me with a probem. I want to use my own sorting routine within a SQL-Query.

Let me explain....

I use the following table (DATA):

USERNAME STATUS PRIO
Michael Open Middle
Michael Open High
Tom Closed
Michael Open Low

The first thing I want to do is sort it by USERNAME (order by USERNAME) alfabeticly. The second thing I want to do is order it by PRIO. But when I use -order by PRIO- the result is will be alfabeticly: High, Low Middle. But the thing i want to have looks like this:

USERNAME STATUS PRIO
Michael Open Low
Michael Open Middle
Michael Open High
Tom Closed

So I should define an other sorting technique. Is this possible within a SQL-Query? And ifso, how do I do it?

Thanx a lot!

HauntDog <..>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-11-07 : 07:12:25

[code]
select
username,
status,
prio
from
data
order by
username asc,
case
when prio = 'low' then 0
when prio = 'middle' then 1
when prio = 'high' then 2
else 47
end
[/code]

Jay White
{0}

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-11-07 : 07:15:06

Hi

Yes, you can use the CASE statement for this :

Select USERNAME, STATUS, PRIO
FROM data
ORDER BY CASE PRIO
WHEN 'Low' THEN 1
WHEN 'Middle' THEN 2
WHEN 'High' THEN 3 END

You can delve further into this technique here http://www.sqlteam.com/item.asp?ItemID=2209

Hope that helps

Damian

EDIT : Damn, sniped...

HauntDog
Starting Member

2 Posts

Posted - 2003-11-07 : 09:07:00

Thnx A lot. It works great!

HauntDog <..>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources