the needed rows into that:
INSERT INTO tmp_weekly_lineup (tournamentId, week, userId, modDate)
SELECT ul.tournamentId, ul.week, ul.userid, max(ul.modDate) AS modDate
FROM users_lineups ul
WHERE ul.tournamentId = 1
AND ul.week = 1
AND ul.submitted = 1
GROUP BY ul.tournamentId, ul.week, ul.submitted, ul.userid
This gives me one row per user, with the highest date where the submitted
flag is true. Then, I just join back to the lineup tables using the temp
table as my index to get the actual lineup:
SELECT ul.userId, ul.week, ul.tournamentId, trim(ul.QB), trim(ul.RB1),
trim(ul.RB2), trim(ul.WR1), trim(ul.WR2), trim(ul.WR3), trim(ul.TE),
trim(ul.K), trim(ul.DEF)
FROM users_lineups ul
INNER JOIN tmp_weekly_lineup twl ON twl.tournamentId = ul.tournamentId AND
twl.week = ul.week AND twl.userId = ul.userId AND twl.modDate = ul.modDate
WHERE ul.submitted = 1
AND ul.tournamentId = 1
AND ul.week = 1
Then I truncate the temporary table for the next go 'round. This is
apparently how it is suggested (by O'Reilly's MySql Cookbook) that you do
reporting when you're using MySQL 3.xx due to the lack of subquries or views
or other helpful database features. I tried doing self-joins but I just
couldn't figure it out that way. That's generally how I would solve similar
problems, but this one just eluded me. Thanks for the help though.
-----Original Message-----
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 8:16 AM
To: CF-Talk
Subject: Re: mysql query help
You know, I've crafted two responses to this already (this is the third)
and both ended up being wrong after I thought about it for a while.
Someone else may be able to post the "right" solution, but in the
interim, you could
<CFQUERY NAME="getrelevantrows">
SELECT user, max(lineupid) as maxlineupid FROM tablename
group by user
</CFQUERY>
<CFLOOP QUERY="getrelevantrows">
<CFQUERY NAME="getspecificrow">
SELECT * FROM tablename
WHERE user=#user# and lineupid=#maxlineupid#
</CFQUERY>
<CFOUTPUT QUERY="getspecificrow">
<!--- output the row data, or user CFLOOP to
construct a new query object to work with
containing all the right data --->
</CFOUTPUT>
</CFLOOP>
I bet there's a better way though. Maybe possible by joining the table
to itself or something...
- Rick
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
