REQ How would I compare multiple date fields in one table to find the latest entry Opps

sorry i didn't explain it correctly before
my table is like this

example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)

I know that Mysql query order by will compare records on a specific date, but how do i compare
multiple fields within the same record. Want to find the latest date within the record..

I noticed that Message-ID: <uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com>
from Gleep contained the following:

>example fields:
>ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)

Ah. I was right about the normalisation.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Geoff Berrow wrote:
> I noticed that Message-ID: <uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com>
> from Gleep contained the following:
>
>
>>example fields:
>>ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)
>
>
> Ah. I was right about the normalisation.
>

Yes, this is what happens when programmers design databases... :)

M.

On Tue, 01 Jun 2004 21:45:52 GMT, Michael Austin <maustin@firstdbasource.com> wrote:

>Geoff Berrow wrote:
>> I noticed that Message-ID: <uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com>
>> from Gleep contained the following:
>>
>>
>>>example fields:
>>>ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)
>>
>>
>> Ah. I was right about the normalisation.
>>
>
>Yes, this is what happens when programmers design databases... :)
>
>M.

Yeah go ahead and bag on me. It's just that you don't understand how my tables are structured. If I
were in fact to have this thing perfectly normalised with having zero empty fields. I'd end up with
500+ tables. I do understand what normalization is and how to structure them, but it doesn't cover
all the odd ball scenarios programmers faced with. I'd rather sacrifice some empty cells than to
have hundreds of complex join staments or a table grid just to contain the fields for other tables.
It not worth the headache.

"Michael Austin" <maustin@firstdbasource.com> wrote in message
news:kO6vc.2231$235.2084@newssvr24.news.prodigy.co m...
> Geoff Berrow wrote:
> > I noticed that Message-ID: <uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com>
> > from Gleep contained the following:
> >
> >
> >>example fields:
> >>ID name username outcome date1 date2 date3 (etc..) - date15
price1 price2 price3 (etc..)
> >
> >
> > Ah. I was right about the normalisation.
> >
>
> Yes, this is what happens when programmers design databases... :)

Competent programmers have no problem designing properly normalised
databases. I have seen databases designed by incompetent DBAs which are
virtually unusable.

--
Tony Marston

http://www.tonymarston.net

"Gleep" <Gleep@Gleep.com> wrote in message
news:ofvpb0trh4nb07cfqujr471jmp1eskalt2@4ax.com...
> On Tue, 01 Jun 2004 21:45:52 GMT, Michael Austin
<maustin@firstdbasource.com> wrote:
>
> >Geoff Berrow wrote:
> >> I noticed that Message-ID: <uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com>
> >> from Gleep contained the following:
> >>
> >>
> >>>example fields:
> >>>ID name username outcome date1 date2 date3 (etc..) - date15
price1 price2 price3 (etc..)
> >>
> >>
> >> Ah. I was right about the normalisation.
> >>
> >
> >Yes, this is what happens when programmers design databases... :)
> >
> >M.
>
> Yeah go ahead and bag on me. It's just that you don't understand how my
tables are structured. If I
> were in fact to have this thing perfectly normalised with having zero
empty fields. I'd end up with
> 500+ tables.

Surely you mean 500+ records, not tables. So what's the p[roblem with that?

> I do understand what normalization is and how to structure them, but it
doesn't cover
> all the odd ball scenarios programmers faced with. I'd rather sacrifice
some empty cells than to
> have hundreds of complex join staments or a table grid just to contain the
fields for other tables.
> It not worth the headache.

You are missing a very serious point. By de-normalising your database in
order to make it easy in one area you are creating a totally different
problem in another area. I have been designing and using databases for over
20 years and in my experience a de-normalised database has always been a bad
move.

--
Tony Marston

http://www.tonymarston.net

"Gleep" <Gleep@Gleep.com> wrote in message
news:uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com...
> my table is like this
>
> example fields:
> ID name username outcome date1 date2 date3 (etc..) - date15 price1
price2 price3 (etc..)

What everyone else said: Your design is wrong. If it were correct, you could
do this with a simple

SELECT id, name, username, outcome, max(date)
FROM table1 JOIN table2 ON table2.ID = table1.ID
GROUP BY ID, name, username, outcome

Since you don't seem to accept the fact that your table design is a Bad
Thing That Needs To Be Fixed:

If you're not using MySQL 4.1, you could try something like this:

SELECT ID,
CASE
WHEN Date1 > Date2 && Date1 > Date3 && Date1 > Date4 && Date1 > Date5 THEN
Date1
WHEN Date2 > Date1 && Date2 > Date3 && Date2 > Date4 && Date2 > Date5 THEN
Date2
WHEN Date3 > Date1 && Date3 > Date2 && Date3 > Date4 && Date3 > Date5 THEN
Date3
WHEN Date4 > Date1 && Date4 > Date2 && Date4 > Date3 && Date4 > Date5 THEN
Date4
WHEN Date5 > Date1 && Date5 > Date2 && Date5 > Date3 && Date5 > Date4 THEN
Date5
END AS MaxDate
FROM YuckyTable

I just tested this on a table with five date columns and it works correctly.
I'll leave it to you to extend it to 15 date columns.

If you're using MySQL 4.1, you could use a subquery along these lines:
(Note: I'm writing this as I would write it for MS-SQL Server, although I
wouldn't write it this way for MS-SQL Server since I wouldn't design the
tables this way. Translation: This is untested and may have errors.)

SELECT id, name, username, outcome, max(date)
FROM table JOIN
(
SELECT id, date1 FROM table
UNION
SELECT id, date2 FROM table
UNION
SELECT id, date3 FROM table
UNION
SELECT id, date4 FROM table
UNION
SELECT id, date5 FROM table
UNION
SELECT id, date6 FROM table
UNION
SELECT id, date7 FROM table
UNION
SELECT id, date8 FROM table
UNION
SELECT id, date9 FROM table
UNION
SELECT id, date10 FROM table
UNION
SELECT id, date11 FROM table
UNION
SELECT id, date12 FROM table
UNION
SELECT id, date13 FROM table
UNION
SELECT id, date14 FROM table
UNION
SELECT id, date15 FROM table
) table2 ON table2.ID = table.ID
GROUP BY ID, name, username, outcome

Steve
--
Steven C. Gallafent - The Computer Guy, Inc.
steve@compguy.com - http://www.compguy.com/

Gleep <Gleep@Gleep.com> wrote in message news:<uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com>. ..
> sorry i didn't explain it correctly before
> my table is like this
>
> example fields:
> ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)
>
> I know that Mysql query order by will compare records on a specific date, but how do i compare
> multiple fields within the same record. Want to find the latest date within the record..

Not sure, if it's possible... But, surely possible with a UDF
<http://dev.mysql.com/doc/mysql/en/Adding_UDF.html> (FWIW, In SQLite
UDF is much simpler, we may use PHP functions itself).

--
| Just another PHP saint |
Email: rrjanbiah-at-Y!com

"Steven C. Gallafent" <steve@compguy.com> wrote in message news:<10bro4aeftkq50@corp.supernews.com>...
> "Gleep" <Gleep@Gleep.com> wrote in message
> news:uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com...
> > my table is like this
> >
> > example fields:
> > ID name username outcome date1 date2 date3 (etc..) - date15 price1
> price2 price3 (etc..)
>
> What everyone else said: Your design is wrong. If it were correct, you could
> do this with a simple
>
> SELECT id, name, username, outcome, max(date)
> FROM table1 JOIN table2 ON table2.ID = table1.ID
> GROUP BY ID, name, username, outcome
>
> Since you don't seem to accept the fact that your table design is a Bad
> Thing That Needs To Be Fixed:
>
> If you're not using MySQL 4.1, you could try something like this:
>
> SELECT ID,
> CASE

<snip: some query hacks>

Very nice post indeed. Visit this group often :-)

--
| Just another PHP saint |
Email: rrjanbiah-at-Y!com

"Gleep" <Gleep@Gleep.com> wrote in message
news:uthpb0dfh8qn6m6chmfv2jeuso41dnajb0@4ax.com...
> sorry i didn't explain it correctly before
> my table is like this
>
> example fields:
> ID name username outcome date1 date2 date3 (etc..) - date15 price1
price2 price3 (etc..)
>
> I know that Mysql query order by will compare records on a specific date,
but how do i compare
> multiple fields within the same record. Want to find the latest date
within the record..

While I concur with normalization comments...

select greatest(date1, date2, date3, date4... date15) as mygreatest
from myfile

will return a record for each row showing the greatest date for each row.

select *
from myfile
order by greatest(date1, date2, date3, date4... date15)) desc
limit 1

will return the first record having the highest date of all 15 dates across
all records.

Is that what you wanted?

- Virgil