|
|
|
|
Thread Tools | Display Modes |
02-16-2005, 05:15 PM | #1 |
Scooby Newbie
Member#: 10960
Join Date: Oct 2001
Chapter/Region:
NWIC
Vehicle:2002 Impreza WRX Silver |
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.
|
02-16-2005, 05:17 PM | #2 |
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 |
Can you do an inner join on a query?
|
02-16-2005, 05:21 PM | #3 |
Scooby Newbie
Member#: 10960
Join Date: Oct 2001
Chapter/Region:
NWIC
Vehicle:2002 Impreza WRX Silver |
yes, MySQL 4.13 supports it
|
02-16-2005, 05:21 PM | #4 |
Scooby Newbie
Member#: 15103
Join Date: Feb 2002
Chapter/Region:
VIC
Location: Vancouver, Canada
Vehicle:2000 2.5RS BRP |
Try this?
select distinct name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate); |
02-16-2005, 05:26 PM | #5 |
Scooby Newbie
Member#: 10960
Join Date: Oct 2001
Chapter/Region:
NWIC
Vehicle:2002 Impreza WRX Silver |
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 | +----------+---------------------+-------+------+------+ |
02-16-2005, 05:27 PM | #6 |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
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. |
02-16-2005, 05:29 PM | #7 | |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
Quote:
|
|
02-16-2005, 05:37 PM | #8 |
Scooby Newbie
Member#: 7121
Join Date: Jun 2001
Chapter/Region:
MWSOC
Location: D2F.1 = D2F.2, D2F.3 = D2F.4
|
Just make another table with name and most recent actiondate.
|
02-16-2005, 05:41 PM | #9 |
Scooby Specialist
Member#: 31976
Join Date: Jan 2003
Location: Philadelphia, PA
Vehicle:2004 WRX STi World Rally Blue |
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.
|
02-16-2005, 05:52 PM | #10 | |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
Quote:
And you should never store what you can calculate. |
|
02-16-2005, 05:59 PM | #11 | |
Scooby Newbie
Member#: 7121
Join Date: Jun 2001
Chapter/Region:
MWSOC
Location: D2F.1 = D2F.2, D2F.3 = D2F.4
|
Quote:
|
|
02-16-2005, 06:03 PM | #12 |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
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.
|
02-16-2005, 06:20 PM | #13 |
Scooby Newbie
Member#: 37288
Join Date: May 2003
Chapter/Region:
RMIC
Location: Colorado
Vehicle:2005 G35 |
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 ....."?
|
02-16-2005, 06:46 PM | #14 | |||||
Scooby Newbie
Member#: 10960
Join Date: Oct 2001
Chapter/Region:
NWIC
Vehicle:2002 Impreza WRX Silver |
Quote:
Quote:
Quote:
Quote:
Quote:
|
|||||
02-16-2005, 06:48 PM | #15 |
Scooby Newbie
Member#: 34852
Join Date: Apr 2003
Location: San Diego, CA
Vehicle:04 WRX STI White / Gold |
Can you do more than one query?
-Ollie |
02-16-2005, 06:52 PM | #16 |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
mysql> select name, actionDate, x, y, z from tbl group by name HAVING actionDate=MAX(actionDate) GROUP BY Name;
Did you try that? |
02-16-2005, 06:53 PM | #17 | |
Scooby Specialist
Member#: 15384
Join Date: Feb 2002
Chapter/Region:
NWIC
Location: Portland, Oregon
|
Quote:
you need to pm me your address... got something to send you. |
|
02-16-2005, 06:55 PM | #18 |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
snail-mail?
You gonna parcel-post me your woman? |
02-16-2005, 06:56 PM | #19 | |
Scooby Guru
Member#: 7977
Join Date: Jul 2001
Chapter/Region:
MAIC
Location: Sterling, VA
Vehicle:100% Abuse |
Quote:
|
|
02-16-2005, 06:56 PM | #20 | |
Scooby Specialist
Member#: 15384
Join Date: Feb 2002
Chapter/Region:
NWIC
Location: Portland, Oregon
|
Quote:
|
|
02-16-2005, 06:57 PM | #21 | |
Scooby Guru
Member#: 7977
Join Date: Jul 2001
Chapter/Region:
MAIC
Location: Sterling, VA
Vehicle:100% Abuse |
Quote:
|
|
02-16-2005, 06:59 PM | #22 |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
Yeah, I'm taking wild stabs now. I'm spoiled by Query Design Grid.
|
02-16-2005, 06:59 PM | #23 | |
Scooby Specialist
Member#: 22958
Join Date: Aug 2002
Chapter/Region:
SWIC
Location: Las Vegas
Vehicle:2017 Forester 2.5 Li Silver |
Quote:
|
|
02-16-2005, 07:09 PM | #24 |
Scooby Specialist
Member#: 6997
Join Date: May 2001
Chapter/Region:
South East
Location: lahrnceville, GA
|
does mySql support local variables?
declare @var datetime select @var = max(actiondate) from tbl select * from tbl where actiondate=@var |
02-16-2005, 07:11 PM | #25 | |
Scooby Newbie
Member#: 10960
Join Date: Oct 2001
Chapter/Region:
NWIC
Vehicle:2002 Impreza WRX Silver |
Quote:
|
|
Thread Tools | |
Display Modes | |
|
|
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 |