EasyCFM.COM - The ColdFusion Community! About Us!

New Member
Posts: 4
Joined: Dec 2005

I am creating an events calendar from an existing database and have it almost done. My problem comes with the ORDER BY syntax. Each event in the database has 4 event time fields: Time1, time2, time3, time4. I've got everything to show up on the correct day but I cannot get the events to show in order by time. I've used the following syntax:
ORDER BY
time1,
time2,
time3,
time4,
event Asc

And I get all the time1 then all the time2 etc. as would be expected.

Is there anyway to combine the 'time' fields into one big field and sort?

Can't seem to figure this one out! Any help that you could give would be greatly appreciated!

Melanie

mquack

07-13-2006 @ 11:21 AM

Moderator
Posts: 1544
Joined: Jan 2005

Is there a particular reason why you need 4 time fields for your events? I obviously do not know your application's requirements, etc, but in my mind there should be only one time field.

As to your specific question, you can write some functions and use QofQ to combine all of them into one big order by, but it might get a bit messy.

http://www.rachelqueensg.com

krisbrixon

07-13-2006 @ 1:52 PM

Senior Member
Posts: 233
Joined: Feb 2005

I agree with mquack that this sounds like a table not normalized. However, assuming you can not change the table:

Try "UNION ALL":

SELECT thisField,thatField, time1 AS eventTime
FROM myTable
UNION ALL
SELECT thisField,thatField, time2 AS eventTime
FROM myTable
UNION ALL
SELECT thisField,thatField, time3 AS eventTime
FROM myTable
UNION ALL
SELECT thisField,thatField, time4 AS eventTime
FROM myTable
ORDER BY eventTime ASC, event ASC

Kris Brixon
www.brixontech.com

mliechty

07-13-2006 @ 3:48 PM