Predefined sort order

Hi
is there a way of sorting elements in a predefined order like
select * from currencies
order by ('eur', 'can' ,'yen', *)
so that eur, can,yen comes first and then the rest?
(without an sp)
greets mikeTry something like this:
select <columns>
,PrimarySortOrder
= case <currency column>
when 'eur' then 100
when 'can' then 200
when 'yen' then 300
else 999
end
from currencies
order by PrimarySortOrder
,<currency column>
ML
http://milambda.blogspot.com/|||peppi911@.hotmail.com wrote on 5 Jan 2006 02:20:44 -0800:

> Hi
> is there a way of sorting elements in a predefined order like
> select * from currencies
> order by ('eur', 'can' ,'yen', *)
> so that eur, can,yen comes first and then the rest?
> (without an sp)
You haven't provided DDL, so I'm going to make it up as I go along, you'll
have to edit to fit your database.
How about using CASE? Assuming the column to sort by is called currency:
SELECT * FROM currencies
ORDER BY
CASE WHEN currency = 'eur' THEN 1
ELSE WHEN currency = 'can' THEN 2
ELSE WHEN currency = 'yen' THEN 3
ELSE 4
END
If you end up having a long list of currencies to sort by this will end up
getting messy. You could do this using a joined table to make it much easier
to define a long list of currencies to sort by, and easily change the sort
order without changing your query.
CREATE TABLE [dbo].[currencysort] (
[currency] [varchar] (3) NOT NULL ,
[sort] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[currencysort] ADD
CONSTRAINT [PK_currencysort] PRIMARY KEY CLUSTERED
(
[currency]
) ON [PRIMARY]
GO
(I've been lazy and used the EM script tables wizard).
INSERT INTO currencysort VALUES('eur',1)
INSERT INTO currencysort VALUES('can',2)
INSERT INTO currencysort VALUES('yen',3)
Now, assuming that you have a field called currency in your currencies
table, you can do something like this:
SELECT currencies.*
FROM currencies LEFT JOIN currencysort ON currencies.currency =
currencysort.currency
ORDER BY COALESCE(currencysort.sort,99)
There are likely much more efficient ways to do this, but someone else can
come up with those :)
Dan|||create table #t
(
[id] int not null primary key,
col varchar(10)
)
insert into #t values (1,'dol')
insert into #t values (2,'rub')
insert into #t values (3,'can')
insert into #t values (4,'yen')
insert into #t values (5,'shek')
insert into #t values (6,'pnd')
insert into #t values (7,'krn')
insert into #t values (8,'eur')
--> order by ('eur', 'can' ,'yen', *)
select * from #t
order by
case when col = 'eur' then 2
when col = 'can' then 1
when col = 'yen' then 0
end desc
<peppi911@.hotmail.com> wrote in message
news:1136456444.177808.131830@.z14g2000cwz.googlegroups.com...
> Hi
> is there a way of sorting elements in a predefined order like
> select * from currencies
> order by ('eur', 'can' ,'yen', *)
> so that eur, can,yen comes first and then the rest?
> (without an sp)
>
> greets mike
>|||Hello!
thanks for your help, thats brilliant!
The page is already working!
regards,
mikesql