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
>
