order by day

Hi,
I have a query but I would like the result to be:
sunday monday tuesday wednesday thursday friday saturday
44 157 153 222 74 455 22
how can i assure that it is sorted by day' Always first sunday, then
monday, ...
FrRead up on
SET DATEFIRST
within Books Online
Keith Kratochvil
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:OOB3oELhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a query but I would like the result to be:
> sunday monday tuesday wednesday thursday friday saturday
> 44 157 153 222 74 455 22
> how can i assure that it is sorted by day' Always first sunday, then
> monday, ...
> Fr
>|||Something like
create table #t (s int, m int) --sunday,monday...
insert into #t values (1,20)
select * from
(
select s as days ,'1'as num from #t
union all
select m, '2' from #t
) as der order by num desc --asc
"Frederik Vanderhaeghe" <frederikvanderhaeghe@.gmail.com> wrote in message
news:OOB3oELhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a query but I would like the result to be:
> sunday monday tuesday wednesday thursday friday saturday
> 44 157 153 222 74 455 22
> how can i assure that it is sorted by day' Always first sunday, then
> monday, ...
> Fr
>|||Hi Fred... If you dont want to use temp tables or change the SQL setting (se
t
datefirst). You could use the following Query.
SELECT *
from YourTable
ORDER BY
CASE
DATENAME(dw,YourDateColumn)
WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Saturday' THEN 7
END
"Frederik Vanderhaeghe" wrote:

> Hi,
> I have a query but I would like the result to be:
> sunday monday tuesday wednesday thursday friday saturday
> 44 157 153 222 74 455 22
> how can i assure that it is sorted by day' Always first sunday, then
> monday, ...
> Fré
>
>|||I resolved it already by using:
order by datepart(day, datum)
"Sha Anand" <ShaAnand@.discussions.microsoft.com> wrote in message
news:00EE2CFC-9849-4399-A371-D4CA8C1585A6@.microsoft.com...
> Hi Fred... If you dont want to use temp tables or change the SQL setting
> (set
> datefirst). You could use the following Query.
> SELECT *
> from YourTable
> ORDER BY
> CASE
> DATENAME(dw,YourDateColumn)
> WHEN 'Sunday' THEN 1
> WHEN 'Monday' THEN 2
> WHEN 'Tuesday' THEN 3
> WHEN 'Wednesday' THEN 4
> WHEN 'Thursday' THEN 5
> WHEN 'Friday' THEN 6
> WHEN 'Saturday' THEN 7
> END
>
>
>
> "Frederik Vanderhaeghe" wrote:
>sql