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

[Gleep]

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..

[Geoff Berrow]

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

I noticed that Message-ID: <uthpb0dfh8qn6m 6chmfv2jeuso41d najb0@4ax.com>
from Gleep contained the following:
[color=blue]
>example fields:
>ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)[/color]

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/

[Michael Austin]

Re: REQ How would I compare multiple date fields in one table tofind the latest entry Opps

Geoff Berrow wrote:[color=blue]
> I noticed that Message-ID: <uthpb0dfh8qn6m 6chmfv2jeuso41d najb0@4ax.com>
> from Gleep contained the following:
>
>[color=green]
>>example fields:
>>ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)[/color]
>
>
> Ah. I was right about the normalisation.
>[/color]

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

M.

[Gleep]

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

On Tue, 01 Jun 2004 21:45:52 GMT, Michael Austin <maustin@firstd basource.com> wrote:
[color=blue]
>Geoff Berrow wrote:[color=green]
>> I noticed that Message-ID: <uthpb0dfh8qn6m 6chmfv2jeuso41d najb0@4ax.com>
>> from Gleep contained the following:
>>
>>[color=darkred]
>>>example fields:
>>>ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)[/color]
>>
>>
>> Ah. I was right about the normalisation.
>>[/color]
>
>Yes, this is what happens when programmers design databases... :)
>
>M.[/color]

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.

[Tony Marston]

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


"Michael Austin" <maustin@firstd basource.com> wrote in message
news:kO6vc.2231 $235.2084@newss vr24.news.prodi gy.com...[color=blue]
> Geoff Berrow wrote:[color=green]
> > I noticed that Message-ID: <uthpb0dfh8qn6m 6chmfv2jeuso41d najb0@4ax.com>
> > from Gleep contained the following:
> >
> >[color=darkred]
> >>example fields:
> >>ID name username outcome date1 date2 date3 (etc..) - date15[/color][/color][/color]
price1 price2 price3 (etc..)[color=blue][color=green]
> >
> >
> > Ah. I was right about the normalisation.
> >[/color]
>
> Yes, this is what happens when programmers design databases... :)[/color]

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

[Tony Marston]

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


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

Surely you mean 500+ records, not tables. So what's the p[roblem with that?
[color=blue]
> I do understand what normalization is and how to structure them, but it[/color]
doesn't cover[color=blue]
> all the odd ball scenarios programmers faced with. I'd rather sacrifice[/color]
some empty cells than to[color=blue]
> have hundreds of complex join staments or a table grid just to contain the[/color]
fields for other tables.[color=blue]
> It not worth the headache.[/color]

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

[Steven C. Gallafent]

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

"Gleep" <Gleep@Gleep.co m> wrote in message
news:uthpb0dfh8 qn6m6chmfv2jeus o41dnajb0@4ax.c om...[color=blue]
> my table is like this
>
> example fields:
> ID name username outcome date1 date2 date3 (etc..) - date15 price1[/color]
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.c om - http://www.compguy.com/

[R. Rajesh Jeba Anbiah]

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

Gleep <Gleep@Gleep.co m> wrote in message news:<uthpb0dfh 8qn6m6chmfv2jeu so41dnajb0@4ax. com>...[color=blue]
> 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..[/color]

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

[R. Rajesh Jeba Anbiah]

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

"Steven C. Gallafent" <steve@compguy. com> wrote in message news:<10bro4aef tkq50@corp.supe rnews.com>...[color=blue]
> "Gleep" <Gleep@Gleep.co m> wrote in message
> news:uthpb0dfh8 qn6m6chmfv2jeus o41dnajb0@4ax.c om...[color=green]
> > my table is like this
> >
> > example fields:
> > ID name username outcome date1 date2 date3 (etc..) - date15 price1[/color]
> 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[/color]

<snip: some query hacks>

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

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

[Virgil Green]

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

"Gleep" <Gleep@Gleep.co m> wrote in message
news:uthpb0dfh8 qn6m6chmfv2jeus o41dnajb0@4ax.c om...[color=blue]
> 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[/color]
price2 price3 (etc..)[color=blue]
>
> I know that Mysql query order by will compare records on a specific date,[/color]
but how do i compare[color=blue]
> multiple fields within the same record. Want to find the latest date[/color]
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