Please permit me to ask another question, unrelated to this issue:

I have a MySQL table with the following fields

ID (int 11) (PK)
Company (varchar 50)
Contactdate (date)
Formdate (date)
Signdate (date)

I would like make a select query that order the records by the most
recent date (i.e. descending) of any of the three date fields and then
by company name. Some date fields may be empty (null) A record must not
be listed more once.

For example, if I have the following data:

ID, Company, Contactdate, Formdate, Signdate
1, acme1, 2006-01-01, 2006-01-10, Null
2, acme2, 2006-01-01, 2006-01-11, 2006-01-12
3, acme3, 2006-01-08, 2006-01-09, 2006-01-09

The result should be

2, acme2
1, acme1
3, acme3

In other words, the records should by ordered by the the 'latest date
field activity' of each record.

If it is complicated to use null values I can also set the default
value of the date fields to something like '1900-01-01'.

Thanks again,

TB


TB wrote:
> It worked perfectly.
>
> Thanks a lot!
>
> TB
>
>
> Peter H. Coffin wrote:
> > On 21 Jul 2006 05:51:22 -0700, TB wrote:
> > > Thanks for replying. The truth is I can actually add another column to
> > > the table 'extratable' which contains the same unique values as are
> > > being used in the primary key column in 'maintable'
> > >
> > > I would therefore have:
> > >
> > > maintable:
> > > ID (PK) (int 11)
> > > firstname (varchar 40)
> > > lastname (varchar 40)
> > > phone (varchar 20)
> > > fax (varchar 20)
> > >
> > > extratable
> > >
> > > ID (int 11)
> > > cellphone (varchar 20)
> > >
> > > I can make ID the PK of extratable.
> > >
> > > Does that change the situation?
> >
> > That makes it easy. You first alter the table to add the column to house
> > the data you want to add, then update that coloumn to contain the value
> > of the other table.
> >
> > ALTER TABLE maintable
> > ADD cellphone VARCHAR(20) AFTER fax;
> >
> > UPDATE maintable JOIN extratable ON maintable.id = extratable.id
> > SET maintable.cellphone = extratable.cellphone;
> >
> > I haven't tested the above, but that should give you the idea.
> >
> > --
> > 81. If I am fighting with the hero atop a moving platform, have disarmed him,
> > and am about to finish him off and he glances behind me and drops flat, I
> > too will drop flat instead of quizzically turning around to find out what
> > he saw. --Peter Anspach's list of things to do as an Evil Overlord