472,800 Members | 1,093 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,800 software developers and data experts.

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.
Jul 17 '05 #1
4 5046
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Gleep | last post by:
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...
4
by: Richard Hollenbeck | last post by:
I'm trying to write some code that will convert any of the most popular standard date formats twice in to something like "dd Mmm yyyy" (i.e. 08 Jan 1908) and compare the first with the second and...
3
by: Fatz | last post by:
I have a table with a Date Field. This field is populated with the date and time an entry was made to the table. I am looking to create a query that pulls only the most recent record. I am...
5
by: QBCM | last post by:
I am trying to create a report by selecting three date fields from one table with records between a start date and end date. I have tried to adapt one of Allen Browne's scripts as follows but it...
2
by: sparks | last post by:
I am trying to find valid records in a table but I must compare a date field to a date stored in the table I can see where this is wrong (ok I guess its not text) "= """ & Me.date & """" so...
13
by: paquer | last post by:
Ok' I have 2 tables. The first being my main table where each record has a 'FigureID' txt box. (Indexed - No duplicates) The second being my sub table where reporting is entered for these...
6
by: Ledmark | last post by:
Hello - I am in a class for Access 2007 Database apllication design and we are covering types of Validation rules. We have a problem that I'm trying to solve but have no idea how to go about writing...
2
tuxalot
by: tuxalot | last post by:
I have a textbox, on with a record source . FrmMain is a tabbed form showing injury data for a given EmployeesID. I have a textbox, on a subform . with as the record source. is entered by a...
2
by: Chellie | last post by:
I am using Access 2007. I have two date fields (date receievd and date complete). I need to be able to run a query and find out who is not completing their work within two days of receipt. I tried...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.