473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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

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..
Jul 17 '05 #1
9 3176
I noticed that Message-ID: <ut********************************@4ax.com>
from Gleep contained the following:
example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)


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/
Jul 17 '05 #2
Geoff Berrow wrote:
I noticed that Message-ID: <ut********************************@4ax.com>
from Gleep contained the following:

example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)

Ah. I was right about the normalisation.


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

M.
Jul 17 '05 #3
On Tue, 01 Jun 2004 21:45:52 GMT, Michael Austin <ma*****@firstdbasource.com> wrote:
Geoff Berrow wrote:
I noticed that Message-ID: <ut********************************@4ax.com>
from Gleep contained the following:

example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)

Ah. I was right about the normalisation.


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

M.


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.

Jul 17 '05 #4

"Michael Austin" <ma*****@firstdbasource.com> wrote in message
news:kO*****************@newssvr24.news.prodigy.co m...
Geoff Berrow wrote:
I noticed that Message-ID: <ut********************************@4ax.com>
from Gleep contained the following:

example fields:
ID name username outcome date1 date2 date3 (etc..) - date15
price1 price2 price3 (etc..)

Ah. I was right about the normalisation.


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


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

Jul 17 '05 #5

"Gleep" <Gl***@Gleep.com> wrote in message
news:of********************************@4ax.com...
On Tue, 01 Jun 2004 21:45:52 GMT, Michael Austin <ma*****@firstdbasource.com> wrote:
Geoff Berrow wrote:
I noticed that Message-ID: <ut********************************@4ax.com>
from Gleep contained the following:
example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1 price2 price3 (etc..)

Ah. I was right about the normalisation.

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

M.


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.
Surely you mean 500+ records, not tables. So what's the p[roblem with that?
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.


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

Jul 17 '05 #6
"Gleep" <Gl***@Gleep.com> wrote in message
news:ut********************************@4ax.com...
my table is like this

example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1

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.
st***@compguy.com - http://www.compguy.com/
Jul 17 '05 #7
Gleep <Gl***@Gleep.com> wrote in message news:<ut********************************@4ax.com>. ..
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..


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
Jul 17 '05 #8
"Steven C. Gallafent" <st***@compguy.com> wrote in message news:<10************@corp.supernews.com>...
"Gleep" <Gl***@Gleep.com> wrote in message
news:ut********************************@4ax.com...
my table is like this

example fields:
ID name username outcome date1 date2 date3 (etc..) - date15 price1

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


<snip: some query hacks>

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

--
| Just another PHP saint |
Email: rrjanbiah-at-Y!com
Jul 17 '05 #9
"Gleep" <Gl***@Gleep.com> wrote in message
news:ut********************************@4ax.com...
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..

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Gleep | last post by:
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...
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: anony | last post by:
Maybe this feature is already out there. I guess you could write triggers to do some of this. Often when designing a database I add a start_date and end_date column to the table. The start_date...
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
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.