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

PostgreSQL

In a Union query, I have initialized one new column for sorting purpose. For
ex :
select ORD = 1 , t1.a, t1.b from table1 as t1
Union
select ORD = 0, t2.a, t2.b from table2 as t2
order by ORD.
ORD column is not present in thw table. It is used here for putting all the
rows of the second query before the rows of first query. The query is
getting executed in Windows which uses SQL 2000 server. But in Linux(RedHat)
when we are using PostgresSQL database, the same query gives error - "Unable
to parse 'ORD' ".
Is this thing not supported in PostgresSQL? If this is not supported then
what is the method of acheiving the same result?
Thanks,
Venkat
Try:
select 1 AS ORD , t1.a, t1.b from table1 as t1
Union
select 0, t2.a, t2.b from table2 as t2
order by ORD
column_alias = expression is T-SQL specific and not ANSI-SQL standard, as
opposed to expression AS column_alias which is standard SQL.
Jacco Schalkwijk
SQL Server MVP
"Venkat" <venkat_kp@.yahoo.com> wrote in message
news:1092226310.253625@.sj-nntpcache-5...
> In a Union query, I have initialized one new column for sorting purpose.
> For
> ex :
> select ORD = 1 , t1.a, t1.b from table1 as t1
> Union
> select ORD = 0, t2.a, t2.b from table2 as t2
> order by ORD.
> ORD column is not present in thw table. It is used here for putting all
> the
> rows of the second query before the rows of first query. The query is
> getting executed in Windows which uses SQL 2000 server. But in
> Linux(RedHat)
> when we are using PostgresSQL database, the same query gives error -
> "Unable
> to parse 'ORD' ".
> Is this thing not supported in PostgresSQL? If this is not supported then
> what is the method of acheiving the same result?
>
> Thanks,
> Venkat
>
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:#YjU$q7fEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Try:
> select 1 AS ORD , t1.a, t1.b from table1 as t1
> Union
> select 0, t2.a, t2.b from table2 as t2
> order by ORD
> column_alias = expression is T-SQL specific and not ANSI-SQL standard, as
> opposed to expression AS column_alias which is standard SQL.
>
Thanks Jacco it worked for me.
regards,
Venkat

PostgreSQL

In a Union query, I have initialized one new column for sorting purpose. For
ex :
select ORD = 1 , t1.a, t1.b from table1 as t1
Union
select ORD = 0, t2.a, t2.b from table2 as t2
order by ORD.
ORD column is not present in thw table. It is used here for putting all the
rows of the second query before the rows of first query. The query is
getting executed in Windows which uses SQL 2000 server. But in Linux(RedHat)
when we are using PostgresSQL database, the same query gives error - "Unable
to parse 'ORD' ".
Is this thing not supported in PostgresSQL? If this is not supported then
what is the method of acheiving the same result?
Thanks,
VenkatTry:
select 1 AS ORD , t1.a, t1.b from table1 as t1
Union
select 0, t2.a, t2.b from table2 as t2
order by ORD
column_alias = expression is T-SQL specific and not ANSI-SQL standard, as
opposed to expression AS column_alias which is standard SQL.
Jacco Schalkwijk
SQL Server MVP
"Venkat" <venkat_kp@.yahoo.com> wrote in message
news:1092226310.253625@.sj-nntpcache-5...
> In a Union query, I have initialized one new column for sorting purpose.
> For
> ex :
> select ORD = 1 , t1.a, t1.b from table1 as t1
> Union
> select ORD = 0, t2.a, t2.b from table2 as t2
> order by ORD.
> ORD column is not present in thw table. It is used here for putting all
> the
> rows of the second query before the rows of first query. The query is
> getting executed in Windows which uses SQL 2000 server. But in
> Linux(RedHat)
> when we are using PostgresSQL database, the same query gives error -
> "Unable
> to parse 'ORD' ".
> Is this thing not supported in PostgresSQL? If this is not supported then
> what is the method of acheiving the same result?
>
> Thanks,
> Venkat
>
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:#YjU$q7fEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Try:
> select 1 AS ORD , t1.a, t1.b from table1 as t1
> Union
> select 0, t2.a, t2.b from table2 as t2
> order by ORD
> column_alias = expression is T-SQL specific and not ANSI-SQL standard, as
> opposed to expression AS column_alias which is standard SQL.
>
Thanks Jacco it worked for me.
regards,
Venkat