I agree with keeping only dates or nulls in a date-type column. One other option, you could use a NULL value for ModifyDate with the COALESCE() function like this
SELECT...., COALESCE(ModifyDate, CreationDate) as recordDate FROM ... WHERE ... ORDER BY recordDate if ModifyDate is not null, that is the value returned, otherwise the COALESCE() function moves to the next term in the list. It will continue checking its value list until it reaches the first non-null value or runs out of records to check. http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html I don't think you will be able to form query this to use an index for the ORDER BY because you are choosing between two different columns to represent the same value (the value you want to sequence your records with). Sorry. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eldo Skaria <[EMAIL PROTECTED]> wrote on 10/04/2004 03:29:46 PM: > Hi Khan, > I would klike to approach ur problem in a diffrent manner. > My suggestion is that, in both the date fields u use dates only. (I > don't know the useabilty of '0' in a date field). While using this, u > have to store the creation time a the modification time, at the > creation of record. This is the technique we are using very extensivey > in our application to store creation and modifcation time as well as > users. Here when there is no modification, the the creation time will > be used for processing, which is same as modfy time. So always you > need to order against the modify time only. You may find it difficult > to adjust ur application so that it processes the modification time in > the new fashion. this has an advantage that, during sorting, it uses > less resources, hence a fster query is resulted. further, if ur table > is having hundreds of thousands of records, then ur order by clause > with functions will become all the more process intensive as compared > to a direct filed sorting. > > reg, > > Eldo. > > On Mon, 4 Oct 2004 10:06:03 +0100, Paul <[EMAIL PROTECTED]> wrote: > > On Mon, 4 Oct 2004 11:03:13 +0200, Martijn Tonies <m. > [EMAIL PROTECTED]> wrote: > > > Hello, > > > > > > > I have two date fields (1095689105) in mysql. One is Creation date and > > > > other is Modify date. If news is not modified its value is 0. How can I > > > > sort my news so modify date is more important (if exists) than creation > > > > date? > > > > > > What about an ORDER BY with a CASE statement that uses > > > the CreationDate if ModifyDate = 0 and ModifyDate if it's <> 0. > > > > > > With regards, > > > > Or use: > > > > ORDER BY if(ModifyDate = 0,1, ModifyDate), CreationDate > > > > Paul > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > -- > Thanks & Regards, > Eldo Skaria > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
