473,700 Members | 2,863 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5124
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.co m> wrote in message
news:pb******** *************** *********@4ax.c om...
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
3207
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 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..
4
5384
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 calculate days, months, and years. This is not for a college course. It's for my own personal genealogy website. I'm stumped about the code. I'm working on it but not making much progress. Is there any free code available anywhere? I know it...
3
2398
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 pretty sure this is done with the DMax function but I don't know how to build the code. Date field is named "Date" Table is named "ABC"
5
3074
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 gives me records outside my date criteria - I guess my strSelect is wrong! I tried strSelect = * but that didn't work either. Private Sub CmndOK_Click() Dim strReport As String 'Name of report to open. Dim strWhere As String 'Where...
2
3039
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 how do you format for a date comparison of a date field in a table and compared to a text box with a date format?
13
8292
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 Figures. Sometimes more than one at a time. I have 4 'FigureID' txt boxes on this subtable.(All Indexed - Duplicates OK) When I set the Master/Child link for the first FigureID on the Subtable I have no problems.
6
8154
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 it. We are building a database for a condo rental company and the rental transaction table includes an arrival date and a departure date. We need to write a table-level validation rule that indicates the departure date is seven days after the...
2
2400
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 user and saved to the table by clicking a save button on the subform. Both date entries are in Short Date format and entered by Date Pickers. I would like an On Click event to fire when the save button is clicked which would compare if and are...
2
5981
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 using datediff but can't figure it out. Any thoughts? I would appreciate it! Chellie
0
8718
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9066
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8963
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7802
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6558
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4400
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4652
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3083
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2022
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.