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

Hey Guys,
I've got a table called Outcomes. With 3 columns and 15 rows

1st col 2nd col 3rdcol
outcome date price

There are 15 rows for each record, each row accounts for a different type of outcome

I'm having trouble with MySQL date comparison. I'm looking for some kind of query that will compare
the all date column and only give me the latest date. Then once I have it, captured that particular
row to output. I've looked in http://forums.devshed.com/search.php? and there are methods to
compare two dates but what about 15? Also take into account that sometimes the dates fields are
empty.

I was thinking should I out put everything to a key=>value array then sort it. Or figure out a
query that does all this for me. Any ideas how to tackle this? Thanx.

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

Gleep wrote:
[color=blue]
> Hey Guys,
> I've got a table called Outcomes. With 3 columns and 15 rows
>
> 1st col 2nd col 3rdcol
> outcome date price
>
> There are 15 rows for each record, each row accounts for a different type of outcome
>
> I'm having trouble with MySQL date comparison. I'm looking for some kind of query that will compare
> the all date column and only give me the latest date. Then once I have it, captured that particular
> row to output. I've looked in http://forums.devshed.com/search.php? and there are methods to
> compare two dates but what about 15? Also take into account that sometimes the dates fields are
> empty.
>
> I was thinking should I out put everything to a key=>value array then sort it. Or figure out a
> query that does all this for me. Any ideas how to tackle this? Thanx.[/color]

How about somehting like:

SELECT 1stcol, 2ndcol, 3rdcol FROM table1 ORDER BY 2ndcol DESC LIMIT 1

That should return only the row with the largest (most recent) date
value, but I'm not quite sure if this is what you are trying to
accomplish...

--
Justin Koivisto - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.

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

On Tue, 01 Jun 2004 18:24:55 GMT, Justin Koivisto <spam@koivi.com> wrote:
[color=blue]
>Gleep wrote:
>[color=green]
>> Hey Guys,
>> I've got a table called Outcomes. With 3 columns and 15 rows
>>
>> 1st col 2nd col 3rdcol
>> outcome date price
>>
>> There are 15 rows for each record, each row accounts for a different type of outcome
>>
>> I'm having trouble with MySQL date comparison. I'm looking for some kind of query that will compare
>> the all date column and only give me the latest date. Then once I have it, captured that particular
>> row to output. I've looked in http://forums.devshed.com/search.php? and there are methods to
>> compare two dates but what about 15? Also take into account that sometimes the dates fields are
>> empty.
>>
>> I was thinking should I out put everything to a key=>value array then sort it. Or figure out a
>> query that does all this for me. Any ideas how to tackle this? Thanx.[/color]
>
>How about somehting like:
>
>SELECT 1stcol, 2ndcol, 3rdcol FROM table1 ORDER BY 2ndcol DESC LIMIT 1
>
>That should return only the row with the largest (most recent) date
>value, but I'm not quite sure if this is what you are trying to
>accomplish...[/color]

yes you're right but i had a brain fart and didn't write down the question correctly. my situation
is more 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..

Answer my own request. Actually what I am working on now is to port out all the fields inot three
separate arrays. then sort on the calanmder array to figure out the latest. Then pull form the other
arrays by matching key values. Unless anyone has a better idea let me know. thanks for your help

GLeep

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

I noticed that Message-ID: <4jfpb0pk8ejfhv48l7e0p99hmrkg7o62pe@4ax.com>
from Gleep contained the following:
[color=blue]
>
>1st col 2nd col 3rdcol
>outcome date price
>
>There are 15 rows for each record, each row accounts for a different type of outcome[/color]
A record is a single row. It sounds like this database may not be
normalised.[color=blue]
>
>I'm having trouble with MySQL date comparison. I'm looking for some kind of query that will compare
>the all date column and only give me the latest date.[/color]

Order by date and just output the first row.

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

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

If your database design was properly normalised the query would be very
simple. Get rid of those repeating fields (date1, date2, ..., price1,
price2, ..) and the problem will disappear.

--
Tony Marston
http://www.tonymarston.net


"Gleep" <Gleep@Gleep.com> wrote in message
news:pbjpb0ttj3cr75rrb8av526h7lv62jlck7@4ax.com...[color=blue]
> On Tue, 01 Jun 2004 18:24:55 GMT, Justin Koivisto <spam@koivi.com> wrote:
>[color=green]
> >Gleep wrote:
> >[color=darkred]
> >> Hey Guys,
> >> I've got a table called Outcomes. With 3 columns and 15 rows
> >>
> >> 1st col 2nd col 3rdcol
> >> outcome date price
> >>
> >> There are 15 rows for each record, each row accounts for a different[/color][/color][/color]
type of outcome[color=blue][color=green][color=darkred]
> >>
> >> I'm having trouble with MySQL date comparison. I'm looking for some[/color][/color][/color]
kind of query that will compare[color=blue][color=green][color=darkred]
> >> the all date column and only give me the latest date. Then once I have[/color][/color][/color]
it, captured that particular[color=blue][color=green][color=darkred]
> >> row to output. I've looked in http://forums.devshed.com/search.php?[/color][/color][/color]
and there are methods to[color=blue][color=green][color=darkred]
> >> compare two dates but what about 15? Also take into account that[/color][/color][/color]
sometimes the dates fields are[color=blue][color=green][color=darkred]
> >> empty.
> >>
> >> I was thinking should I out put everything to a key=>value array then[/color][/color][/color]
sort it. Or figure out a[color=blue][color=green][color=darkred]
> >> query that does all this for me. Any ideas how to tackle this?[/color][/color][/color]
Thanx.[color=blue][color=green]
> >
> >How about somehting like:
> >
> >SELECT 1stcol, 2ndcol, 3rdcol FROM table1 ORDER BY 2ndcol DESC LIMIT 1
> >
> >That should return only the row with the largest (most recent) date
> >value, but I'm not quite sure if this is what you are trying to
> >accomplish...[/color]
>
> yes you're right but i had a brain fart and didn't write down the[/color]
question correctly. my situation[color=blue]
> is more 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..[color=blue]
>
> Answer my own request. Actually what I am working on now is to port out[/color]
all the fields inot three[color=blue]
> separate arrays. then sort on the calanmder array to figure out the[/color]
latest. Then pull form the other[color=blue]
> arrays by matching key values. Unless anyone has a better idea let me[/color]
know. thanks for your help[color=blue]
>
> GLeep
>
>[/color]