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

Prblem to store XML result into an output veriable on SQL 2000

Hi,

I want to store the result of the query

SELECT * FROM Customer FOR XML AUTO,ELEMENTS

Into an output veriable. How will I do this in SQL Server 2000?

I've tried this in simple way like

declare @.x varchar(1000)

set @.x = (select * from customer for xml auto,elements)

select @.x

This is perfectly working in SQL 2005 but throwing error in 2000

also in I've tried this using cursor, TempTable on SQL Server 2000.

Please help me.

You can't able to do this in SQL Server 2000. In SQL Server 2000 we don't have XML datatype. It is introduced from SQL Server 2005 only.

The only possible solution is manullay concatinating the values, but it is very expensive and there is char length limitation (8000) may cause truncation of your data.

|||

Thank you very much.

Prblem to store XML result into an output veriable on SQL 2000

Hi,

I want to store the result of the query

SELECT * FROM Customer FOR XML AUTO,ELEMENTS

Into an output veriable. How will I do this in SQL Server 2000?

I've tried this in simple way like

declare @.x varchar(1000)

set @.x = (select * from customer for xml auto,elements)

select @.x

This is perfectly working in SQL 2005 but throwing error in 2000

also in I've tried this using cursor, TempTable on SQL Server 2000.

Please help me.

You can't able to do this in SQL Server 2000. In SQL Server 2000 we don't have XML datatype. It is introduced from SQL Server 2005 only.

The only possible solution is manullay concatinating the values, but it is very expensive and there is char length limitation (8000) may cause truncation of your data.

|||

Thank you very much.