Welcome to the North American Subaru Impreza Owners Club Monday March 18, 2024
Home Forums Images WikiNASIOC Products Store Modifications Upgrade Garage
NASIOC
Go Back   NASIOC > NASIOC Miscellaneous > Off-Topic

Welcome to NASIOC - The world's largest online community for Subaru enthusiasts!
Welcome to the NASIOC.com Subaru forum.

You are currently viewing our forum as a guest, which gives you limited access to view most discussions and access our other features. By joining our community, free of charge, you will have access to post topics, communicate privately with other members (PM), respond to polls, upload content and access many other special features. Registration is free, fast and simple, so please join our community today!

If you have any problems with the registration process or your account login, please contact us.







* As an Amazon Associate I earn from qualifying purchases. 
* Registered users of the site do not see these ads. 
Reply
 
Thread Tools Display Modes
Old 02-16-2005, 05:15 PM   #1
Alpine
Scooby Newbie
 
Member#: 10960
Join Date: Oct 2001
Chapter/Region: NWIC
Vehicle:
2002 Impreza WRX
Silver

Default SQL gurus, need your help!

This has me stumped for almost a day now... I have a table with the following columns in MySQL 4.13 (some names slightly changed, others omitted).

+------------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------------------+-------+
| name | varchar(25) | | PRI | | |
| actionDate | datetime | | PRI | 0000-00-00 00:00:00 | |
| x | float(8,2) | | | 0.00 | |
| y | float(8,2) | | | 0.00 | |
| z | float(8,2) | | | 0.00 | |
+------------+-------------+------+-----+---------------------+-------+

Users will be entering new values for x,y, and z on different dates for the same "name," so we end up with rows like this:


+----------+---------------------+-------+------+------+
| name | actionDate | x | y | z |
+----------+---------------------+-------+------+------+
| bear | 2005-02-05 11:59:43 | 10.00 | 9.00 | 8.00 |
| axe | 2005-02-04 14:38:17 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 14:54:23 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 14:54:33 | 0.70 | 0.00 | 0.00 |
| axe | 2005-02-04 15:28:54 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 15:29:09 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 15:43:49 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 15:44:34 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 16:02:45 | 0.00 | 0.00 | 0.80 |
| worm | 2005-02-04 16:03:23 | 0.00 | 0.00 | 0.00 |
| worm| 2005-02-04 16:03:28 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-04 16:05:05 | 0.00 | 0.00 | 0.80 |
| axe | 2005-02-04 16:08:50 | 0.00 | 0.00 | 0.80 |
| axe | 2005-02-04 16:09:12 | 0.00 | 0.00 | 0.80 |
| axe | 2005-02-09 12:05:39 | 0.00 | 0.00 | 0.81 |
+----------+---------------------+-------+------+------+


I want to get the most recent values of x, y, and z for each name, ie


+----------+---------------------+-------+------+------+
| name | actionDate | x | y | z |
+----------+---------------------+-------+------+------+
| bear | 2005-02-05 11:59:43 | 10.00 | 9.00 | 8.00 |
| worm| 2005-02-04 16:03:28 | 0.00 | 0.00 | 0.00 |
| axe | 2005-02-09 12:05:39 | 0.00 | 0.00 | 0.81 |
+----------+---------------------+-------+------+------+


I've tried so many combinations of havings and group bys and wheres it's not even funny. The one I thought would work:


mysql> select name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate);
+----------+---------------------+-------+------+------+
| name | actionDate | x | y | z |
+----------+---------------------+-------+------+------+
| bear | 2005-02-05 11:59:43 | 10.00 | 9.00 | 8.00 |
+----------+---------------------+-------+------+------+



And I can't use nested queries or views because this is MySQL 4.13 and it sucks.

Anyone have any ideas, queries, hints, tips other than "use a better db," etc?
* Registered users of the site do not see these ads.
Alpine is offline   Reply With Quote
Sponsored Links
* Registered users of the site do not see these ads.
Old 02-16-2005, 05:17 PM   #2
Hypernoodle
Scooby Specialist
 
Member#: 12391
Join Date: Nov 2001
Chapter/Region: NESIC
Location: YER.MOMS.A.HAIKU.AND.HERES.WHY
Vehicle:
I did her 5 times
then 7 times, then 5 more

Default

Can you do an inner join on a query?
Hypernoodle is offline   Reply With Quote
Old 02-16-2005, 05:21 PM   #3
Alpine
Scooby Newbie
 
Member#: 10960
Join Date: Oct 2001
Chapter/Region: NWIC
Vehicle:
2002 Impreza WRX
Silver

Default

yes, MySQL 4.13 supports it
Alpine is offline   Reply With Quote
Old 02-16-2005, 05:21 PM   #4
wrx-rex
Scooby Newbie
 
Member#: 15103
Join Date: Feb 2002
Chapter/Region: VIC
Location: Vancouver, Canada
Vehicle:
2000 2.5RS
BRP

Default

Try this?

select distinct name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate);
wrx-rex is offline   Reply With Quote
Old 02-16-2005, 05:26 PM   #5
Alpine
Scooby Newbie
 
Member#: 10960
Join Date: Oct 2001
Chapter/Region: NWIC
Vehicle:
2002 Impreza WRX
Silver

Default

Thanks - I'm not 100% sure why this returns the same results as without the "distinct."

mysql> select distinct name, actionDate, x, y, z from tbl group by name having actionDate=MAX(actionDate);
+----------+---------------------+-------+------+------+
| name | actionDate | x | y | z |
+----------+---------------------+-------+------+------+
| bear | 2005-02-05 11:59:43 | 10.00 | 9.00 | 8.00 |
+----------+---------------------+-------+------+------+
Alpine is offline   Reply With Quote
Old 02-16-2005, 05:27 PM   #6
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

I know you don't want to hear this, but you should really have only one observation field in the table, along with an ObservationType field that is valued x, y or z.

Then, since you're using MySQL, you would have to create a query containing three subqueries, each subquery criteria'd on Max(actionDate) and joined by Name.
imprezton is offline   Reply With Quote
Old 02-16-2005, 05:29 PM   #7
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

Quote:
Originally Posted by wrx-rex
Try this?

select distinct name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate);
It isn't working because it first chooses the Max of date, then returns the observation values in that record. What he wants to do is choose the Max(actionDate) for each individual observation type having observation>0, then compile them.
imprezton is offline   Reply With Quote
Old 02-16-2005, 05:37 PM   #8
aod
Scooby Newbie
 
Member#: 7121
Join Date: Jun 2001
Chapter/Region: MWSOC
Location: D2F.1 = D2F.2, D2F.3 = D2F.4
Default

Just make another table with name and most recent actiondate.
aod is offline   Reply With Quote
Old 02-16-2005, 05:41 PM   #9
catass
Scooby Specialist
 
Member#: 31976
Join Date: Jan 2003
Location: Philadelphia, PA
Vehicle:
2004 WRX STi
World Rally Blue

Default

really you should have a table with a numeric (dunno the data types in mysql) id column as a primary key and the name. and then you should have another table with the id (indexed of course) and the actiondate, and x, y, z, so then you can join on the id, and not on a string which is totally slow. and also you are really wasting space by storing those names over and over again.
catass is offline   Reply With Quote
Old 02-16-2005, 05:52 PM   #10
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

Quote:
Originally Posted by aod
Just make another table with name and most recent actiondate.
There can be three different most recent action dates for a single name, though.

And you should never store what you can calculate.
imprezton is offline   Reply With Quote
Old 02-16-2005, 05:59 PM   #11
aod
Scooby Newbie
 
Member#: 7121
Join Date: Jun 2001
Chapter/Region: MWSOC
Location: D2F.1 = D2F.2, D2F.3 = D2F.4
Default

Quote:
Originally Posted by imprezton
There can be three different most recent action dates for a single name, though.

And you should never store what you can calculate.
So you always update the table with the newest recent date rather than inserting it into the table. I realize this is ugly, but the whole damn database is ugly at this point. The easiest way around it is to just create another table that he can reference.
aod is offline   Reply With Quote
Old 02-16-2005, 06:03 PM   #12
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

Then you are storing the same date in two different tables. And if an event occurs that updates the transaction record without updating the redundent table, you have no data integrity.
imprezton is offline   Reply With Quote
Old 02-16-2005, 06:20 PM   #13
coalrabbit
Scooby Newbie
 
Member#: 37288
Join Date: May 2003
Chapter/Region: RMIC
Location: Colorado
Vehicle:
2005 G35

Default

I've spent a good deal of time a while back to do something like this. I couldn't find any way of doing this elegantly without using views or nested queries. I think the only way around this now is for you to make a temporary table each time you run that query to simulate a view, and then deleting that table afterwards. Does that version of MySQL support "Insert Into temptable Select ....."?
coalrabbit is offline   Reply With Quote
Old 02-16-2005, 06:46 PM   #14
Alpine
Scooby Newbie
 
Member#: 10960
Join Date: Oct 2001
Chapter/Region: NWIC
Vehicle:
2002 Impreza WRX
Silver

Default

Quote:
Originally Posted by imprezton
I know you don't want to hear this, but you should really have only one observation field in the table, along with an ObservationType field that is valued x, y or z.

Then, since you're using MySQL, you would have to create a query containing three subqueries, each subquery criteria'd on Max(actionDate) and joined by Name.
This sounds promising but this version of MySQL doesn't support subqueries/nested queries. Also I don't understand your use of "observation field" and "observation type" - do you know any synonyms for that term?

Quote:
Originally Posted by imprezton
It isn't working because it first chooses the Max of date, then returns the observation values in that record. What he wants to do is choose the Max(actionDate) for each individual observation type having observation>0, then compile them.
It's actually a little simpler... I want to choose only max(actionDate) & the corresponding x/y/z/whatever for each name.


Quote:
Originally Posted by aod
Just make another table with name and most recent actiondate.
Unfortunately this actually looks like a good idea Like imprezton says it is duplicating data which I want to avoid but may be necessary.


Quote:
Originally Posted by catass
really you should have a table with a numeric (dunno the data types in mysql) id column as a primary key and the name. and then you should have another table with the id (indexed of course) and the actiondate, and x, y, z, so then you can join on the id, and not on a string which is totally slow. and also you are really wasting space by storing those names over and over again.
I inherited this database... you have no idea the scale of waste involved with this thing. The developers who created/used it never used joins in any of their code.

Quote:
Originally Posted by coalrabbit
I've spent a good deal of time a while back to do something like this. I couldn't find any way of doing this elegantly without using views or nested queries. I think the only way around this now is for you to make a temporary table each time you run that query to simulate a view, and then deleting that table afterwards. Does that version of MySQL support "Insert Into temptable Select ....."?
Yes it does... simulating the "select name,max(actionDate) from tbl group by name" view is increasingly looking like the best option.
Alpine is offline   Reply With Quote
Old 02-16-2005, 06:48 PM   #15
felonious
Scooby Newbie
 
Member#: 34852
Join Date: Apr 2003
Location: San Diego, CA
Vehicle:
04 WRX STI
White / Gold

Default

Can you do more than one query?

-Ollie
felonious is offline   Reply With Quote
Old 02-16-2005, 06:52 PM   #16
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

mysql> select name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate) GROUP BY Name;


Did you try that?
imprezton is offline   Reply With Quote
Old 02-16-2005, 06:53 PM   #17
pjcoregon
Scooby Specialist
 
Member#: 15384
Join Date: Feb 2002
Chapter/Region: NWIC
Location: Portland, Oregon
Default

Quote:
Originally Posted by imprezton
Then you are storing the same date in two different tables. And if an event occurs that updates the transaction record without updating the redundent table, you have no data integrity.
which is why he really should of used a key that is a foreign key references in a date-time table.

you need to pm me your address... got something to send you.
pjcoregon is offline   Reply With Quote
Old 02-16-2005, 06:55 PM   #18
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

snail-mail?

You gonna parcel-post me your woman?
imprezton is offline   Reply With Quote
Old 02-16-2005, 06:56 PM   #19
rogue
Scooby Guru
 
Member#: 7977
Join Date: Jul 2001
Chapter/Region: MAIC
Location: Sterling, VA
Vehicle:
100% Abuse

Default

Quote:
Originally Posted by Alpine
Yes it does... simulating the "select name,max(actionDate) from tbl group by name" view is increasingly looking like the best option.
That's the correct answer.
rogue is offline   Reply With Quote
Old 02-16-2005, 06:56 PM   #20
pjcoregon
Scooby Specialist
 
Member#: 15384
Join Date: Feb 2002
Chapter/Region: NWIC
Location: Portland, Oregon
Default

Quote:
Originally Posted by imprezton
snail-mail?

You gonna parcel-post me your woman?
haha... sending you beer you haven't experienced in sin city.
pjcoregon is offline   Reply With Quote
Old 02-16-2005, 06:57 PM   #21
rogue
Scooby Guru
 
Member#: 7977
Join Date: Jul 2001
Chapter/Region: MAIC
Location: Sterling, VA
Vehicle:
100% Abuse

Default

Quote:
Originally Posted by imprezton
mysql> select name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate) GROUP BY Name;


Did you try that?
This will only display ONE record, the latest... he wants each name's latest.
rogue is offline   Reply With Quote
Old 02-16-2005, 06:59 PM   #22
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

Yeah, I'm taking wild stabs now. I'm spoiled by Query Design Grid.
imprezton is offline   Reply With Quote
Old 02-16-2005, 06:59 PM   #23
imprezton
Scooby Specialist
 
Member#: 22958
Join Date: Aug 2002
Chapter/Region: SWIC
Location: Las Vegas
Vehicle:
2017 Forester 2.5 Li
Silver

Default

Quote:
Originally Posted by pjcoregon
haha... sending you beer you haven't experienced in sin city.
Cool!
imprezton is offline   Reply With Quote
Old 02-16-2005, 07:09 PM   #24
driver8
Scooby Specialist
 
Member#: 6997
Join Date: May 2001
Chapter/Region: South East
Location: lahrnceville, GA
Default

does mySql support local variables?


declare @var datetime

select @var = max(actiondate) from tbl

select * from tbl where actiondate=@var
driver8 is offline   Reply With Quote
Old 02-16-2005, 07:11 PM   #25
Alpine
Scooby Newbie
 
Member#: 10960
Join Date: Oct 2001
Chapter/Region: NWIC
Vehicle:
2002 Impreza WRX
Silver

Default

Quote:
Originally Posted by imprezton
mysql> select name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate) GROUP BY Name;


Did you try that?
That's a syntax error... having always follows a group by.
Alpine is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Xbox360 Gurus! Need your help/input! ITSME4G63 Off-Topic 5 11-24-2006 01:54 AM
Data logging gurus, need your help reading log. vortectoy AccessPort 5 05-22-2006 01:19 AM
OT IT gurus - need your help Leadfoot77 Off-Topic 14 11-11-2005 06:17 PM
Insurance agents/gurus, need your help. aod Off-Topic 16 07-20-2005 12:58 PM
IT and or SCSI gurus need your help ChosenWon Off-Topic 7 11-07-2003 12:36 PM

All times are GMT -4. The time now is 10:32 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Powered by Searchlight © 2024 Axivo Inc.
Copyright ©1999 - 2019, North American Subaru Impreza Owners Club, Inc.

As an Amazon Associate I earn from qualifying purchases.

When you click on links to various merchants on this site and make a purchase, this can result in this site earning a commission
Affiliate programs and affiliations include, but are not limited to, the eBay Partner Network.