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.
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>
> > 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
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
Thanks & Regards,