List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 4 2004 7:41pm
Subject:Re: MySQL Order by 2 date fields
View as plain text  
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 <eldo.skaria@stripped> 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 <pardel@stripped> wrote:
> > On Mon, 4 Oct 2004 11:03:13 +0200, Martijn Tonies <m.
> tonies@stripped> 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/mysql?unsub=1
> > 
> > 
> 
> 
> 
> -- 
> Thanks & Regards,
> Eldo Skaria
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
MySQL Order by 2 date fieldsKhan4 Oct
  • Re: MySQL Order by 2 date fieldsMartijn Tonies4 Oct
    • Re: MySQL Order by 2 date fieldsPaul4 Oct
      • Re: MySQL Order by 2 date fieldsEldo Skaria4 Oct
        • Re: MySQL Order by 2 date fieldsSGreen4 Oct