By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Jul 17 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Gleep wrote:
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.


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 - sp**@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.
Jul 17 '05 #2

P: n/a
On Tue, 01 Jun 2004 18:24:55 GMT, Justin Koivisto <sp**@koivi.com> wrote:
Gleep wrote:
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.


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


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
Jul 17 '05 #3

P: n/a
I noticed that Message-ID: <4j********************************@4ax.com>
from Gleep contained the following:

1st col 2nd col 3rdcol
outcome date price

There are 15 rows for each record, each row accounts for a different type of outcome A record is a single row. It sounds like this database may not be
normalised.
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.


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/
Jul 17 '05 #4

P: n/a
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" <Gl***@Gleep.com> wrote in message
news:pb********************************@4ax.com...
On Tue, 01 Jun 2004 18:24:55 GMT, Justin Koivisto <sp**@koivi.com> wrote:
Gleep wrote:
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.
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...
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

Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.