customized order by

I would like to some how customize my order by clause such that data gets
ordered in a very specific manner.
Please consider the following ddl
set nocount on
go
create table z_my_tbl_del
( i int,
n char(8)
)
go
insert z_my_tbl_del values(1,'aaa')
insert z_my_tbl_del values(1,'aaa')
insert z_my_tbl_del values(2,'bbb')
insert z_my_tbl_del values(2,'bbb')
insert z_my_tbl_del values(3,'ccc')
insert z_my_tbl_del values(3,'ccc')
insert z_my_tbl_del values(4,'ddd')
insert z_my_tbl_del values(4,'ddd')
insert z_my_tbl_del values(5,'eee')
insert z_my_tbl_del values(5,'eee')
go
select * from z_my_tbl_del
/*
/*
i n
-- --
1 aaa
1 aaa
2 bbb
2 bbb
3 ccc
3 ccc
4 ddd
4 ddd
5 eee
5 eee
*/
*/
go
drop table z_my_tbl_del
go
How can I write order by clause such that it orders by bbb first, then ddd
and then it would sort rest of the items regularly.
Please let me know if there is a way of doing it.
TIA...
/*
i n
-- --
2 bbb
2 bbb
4 ddd
4 ddd
1 aaa
1 aaa
3 ccc
3 ccc
5 eee
5 eee
*/ORDER BY CASE n WHEN 'bbb' THEN 1 WHEN 'ddd' THEN 2 ELSE 3 END, i
"sqlster" <trisha@.nospam.nospam> wrote in message
news:9524A826-4A16-4AED-A0E1-F7A4D9AAF4B2@.microsoft.com...
>I would like to some how customize my order by clause such that data gets
> ordered in a very specific manner.
> Please consider the following ddl
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(5,'eee')
> insert z_my_tbl_del values(5,'eee')
> go
> select * from z_my_tbl_del
> /*
> /*
> i n
> -- --
> 1 aaa
> 1 aaa
> 2 bbb
> 2 bbb
> 3 ccc
> 3 ccc
> 4 ddd
> 4 ddd
> 5 eee
> 5 eee
> */
> */
> go
> drop table z_my_tbl_del
> go
> How can I write order by clause such that it orders by bbb first, then ddd
> and then it would sort rest of the items regularly.
> Please let me know if there is a way of doing it.
> TIA...
> /*
> i n
> -- --
> 2 bbb
> 2 bbb
> 4 ddd
> 4 ddd
> 1 aaa
> 1 aaa
> 3 ccc
> 3 ccc
> 5 eee
> 5 eee
> */
>|||Basically, you want 2 levels of ordering with the first level consisting of
an expression that evaluates 'bbb' and 'ddd' at the top of the list.
Modify your query like so:
select
*
from
#z_my_tbl_del
order by
case n
when 'bbb' then 1
when 'ddd' then 2
else 3
end,
n
"sqlster" <trisha@.nospam.nospam> wrote in message
news:9524A826-4A16-4AED-A0E1-F7A4D9AAF4B2@.microsoft.com...
>I would like to some how customize my order by clause such that data gets
> ordered in a very specific manner.
> Please consider the following ddl
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(5,'eee')
> insert z_my_tbl_del values(5,'eee')
> go
> select * from z_my_tbl_del
> /*
> /*
> i n
> -- --
> 1 aaa
> 1 aaa
> 2 bbb
> 2 bbb
> 3 ccc
> 3 ccc
> 4 ddd
> 4 ddd
> 5 eee
> 5 eee
> */
> */
> go
> drop table z_my_tbl_del
> go
> How can I write order by clause such that it orders by bbb first, then ddd
> and then it would sort rest of the items regularly.
> Please let me know if there is a way of doing it.
> TIA...
> /*
> i n
> -- --
> 2 bbb
> 2 bbb
> 4 ddd
> 4 ddd
> 1 aaa
> 1 aaa
> 3 ccc
> 3 ccc
> 5 eee
> 5 eee
> */
>|||1) use table for the ordering
CREATE TABLE SpecialSort
(sort_order INTEGER NOT NULL,
n CHAR(3) NOT NULL);
INSERT INTO S.sort_order VALUES (1, 'bbb');
INSERT INTO S.sort_order VALUES (2, 'ddd');
INSERT INTO S.sort_order VALUES (3, 'aaa');
etc.
SELECT F.*, S.sort_order
FROM Foobar AS F, SpecialSort AS S
WHERE S.n = F.n
ORDER BY S.sort_order;
2) use a string
SELECT F.*, CHARINDEX (n, 'bbbdddaaaccceee') AS sort_order
FROM Foobar
ORDER BY sort_order;|||Why do you want to do this? Just one time? I would suggest that you add a
sort column to your actual table if you want to sort it differently
(especially since the client probably will want to change the sort
sometimes.) If it is just for certain reports, then implement a report sort
order table and you can then change the ordering at will.
create table sortOrder(
reportName varchar(10),
n varchar(8),
sortOrder int,
primary key (reportName, N),
unique (reportName, sortOrder)
)
insert into sortorder values('yours','bbb',1)
insert into sortorder values('yours','ddd',2)
select z_my_tbl_del.*
from z_my_tbl_del
left outer join sortorder
on z_my_tbl_del.n = sortOrder.n
and sortOrder.reportName = 'yours'
order by coalesce(sortorder.sortOrder,2000000000) asc, z_my_tbl_del.n
insert into sortorder values('yours','bbb',1)
insert into sortorder values('yours','ddd',2)
insert into sortorder values('mine','eee',1)
insert into sortorder values('mine','ddd',2)
insert into sortorder values('mine','aaa',3)
select z_my_tbl_del.*
from z_my_tbl_del
left outer join sortorder
on z_my_tbl_del.n = sortOrder.n
and sortOrder.reportName = 'mine'
order by coalesce(sortorder.sortOrder,2000000000) asc, z_my_tbl_del.n
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sqlster" <trisha@.nospam.nospam> wrote in message
news:9524A826-4A16-4AED-A0E1-F7A4D9AAF4B2@.microsoft.com...
>I would like to some how customize my order by clause such that data gets
> ordered in a very specific manner.
> Please consider the following ddl
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(1,'aaa')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(2,'bbb')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(3,'ccc')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(4,'ddd')
> insert z_my_tbl_del values(5,'eee')
> insert z_my_tbl_del values(5,'eee')
> go
> select * from z_my_tbl_del
> /*
> /*
> i n
> -- --
> 1 aaa
> 1 aaa
> 2 bbb
> 2 bbb
> 3 ccc
> 3 ccc
> 4 ddd
> 4 ddd
> 5 eee
> 5 eee
> */
> */
> go
> drop table z_my_tbl_del
> go
> How can I write order by clause such that it orders by bbb first, then ddd
> and then it would sort rest of the items regularly.
> Please let me know if there is a way of doing it.
> TIA...
> /*
> i n
> -- --
> 2 bbb
> 2 bbb
> 4 ddd
> 4 ddd
> 1 aaa
> 1 aaa
> 3 ccc
> 3 ccc
> 5 eee
> 5 eee
> */
>