I was wondering if this would work. I do not know mySql so it is just a guess
from looking at the documentation online:
Select
CASE WS_WeekDay
WHEN 'Monday' THEN 1
WHEN 'Tuesday' THEN 2
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 4
WHEN 'Friday' THEN 5
WHEN 'Saturday' THEN 6
WHEN 'Sunday' THEN 7
END CASE AS Day_of_Week_Sort
group by day_of_week_sort
order by day_of_week_sort
Not sure as I do not have mySql to test it on.
Mario
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 5:02 PM
To: CF-Talk
Subject: RE: How to order by days of the week, but not alphabetically in this
query
Well...
While I was waiting for replies, I went ahead and change the days of the
week in the db field
to "01_Monday" "02_Tuesday" etc. and used #ListLast(GetDays.WeekDay, '_')#
for display.
That way the order by in the query used the numbers for ordering, then I
just display
the day part.
I'll just assign the values of days in the Select form field as "01_Sunday",
"02_Monday", etc...
Seems to work fine so far...
Rick
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 4:39 PM
To: CF-Talk
Subject: RE: How to order by days of the week, but not alphabetically in
this query
cant you just check for the day...have the days in an array
(session.daysArray) in the session scope...where you use 1-7 versus 0-6?
since cf arrays use 1 as beginning, and in your presentation layer, decide
what to show based on the value and then use the daysArray to determine
what
to show?
1- Sunday
2 - Monday
3 - Tuesday
4 - Wednesday
5 Thursday
6 Friday
7 Saturday
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 4:35 PM
To: CF-Talk
Subject: RE: How to order by days of the week, but not alphabetically in
this query
Hmmm...I even converted the column in the db table to tinyint and I've
started using 0, 1, 2, etc. for days of the week (0=Sunday), but still
can't
find any way to change "0" to "Sunday" for display...
Haven't found any way to do it in the MySQL docs...can't believe it's not
possible to convert "0" to "Sunday" or "Sunday" to "0"...
Maybe I have to add "01" to Monday in the db then just display "Monday".
Have "01_Sunday" "02_Monday" for ordering but cut off the "01_" for
display...
How would I do that for display? #ListLast(GetDays.DayofWeek, "_")# ???
Rick
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 4:03 PM
To: CF-Talk
Subject: RE: How to order by days of the week, but not alphabetically in
this query
not too sure about mysql. im the dumb one for assuming sql server :)
but
im sure there is a mysql expert that can shed some light....
tony
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 3:39 PM
To: CF-Talk
Subject: RE: How to order by days of the week, but not alphabetically in
this query
Thanks for the reply, Tony.
I was hoping to avoid another column.
Is there any way in MySQL to convert the name of the weekday to a
numeric
value for ordering?
If not, I guess I should have just used numeric values in the db instead
of
names...
Rick
-----Original Message-----
From: Tony Weeg [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 3:27 PM
To: CF-Talk
Subject: RE: How to order by days of the week, but not alphabetically
in
this query
maybe give another column that is day of week...
so that all wendesdays have day (4) (assuming starting Sunday)?
tony
-----Original Message-----
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 25, 2004 3:21 PM
To: CF-Talk
Subject: How to order by days of the week, but not alphabetically in
this
query
Hi, all...
How do I get this query to order by days of the week according to the
calendar, not alphabetically?
<CFQUERY Name="GetDays" Datasource="#DSN#">
Select WS_WeekDay
from weeklyschedule
group by WS_WeekDay
order by WS_WeekDay
</CFQUERY>
I tried order by DayofWeek(WS_WeekDay) as a guess, but that didn't
work...
Rick
--
Outgoing mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.224 / Virus Database: 261.9.8 - Release Date: 2/24/2004
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
