Wow! What a trick! Harold, I am ashamed for not remembering that log/exp technique myself.
Just out of curiosity, what is the difference between these two: COALESCE(*expression*, 1) IFNULL(*expression*, 1) Right off the bat, I know that COALESCE accepts multiple arguments, while IFNULL always takes two. If I only have two arguments, is there any advantage in using one or the other from any optimization points of view? --- Fyodor Golos Stockworm, Inc. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 26, 2004 8:42 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; news Subject: Re: need help with a complicated join Harold, you win the "EUREKA" prize of the month! I had forgotten all about that silly algebraic trick. This answers another person's post from last week. (I will try to find it again) also looking for a PRODUCT() function. I agree about the unknown performance. If you only need to return adjusted quotes for particular symbols and a date range, you *may* be better off pre-calcuating the LOG()s in a temp table for what you need to quote. You can combine that with the stock symbols and the date ranges (from your main query) to minimize the number of items that the LEFT JOIN ends up processing. something like : CREATE TEMPORARY TABLE tmpSplits SELECT Symbol, date, log(split_from/split_to) as logadj FROM splits WHERE date > *earliest date* AND Symbol in (*list of symbols*) SELECT q.symbol, q.date, q.quote, q.quote * COALESCE(exp(sum(ts.logadj)), 1) FROM quotes q LEFT JOIN tmpSplits ts ON ts.symbol = q.symbol AND ts.date > q.date WHERE q.symbol in (*list of symbols*) and q.date > *earliest date* GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date only benchmarking will truly show which is faster. Once again - A HUGE "way-to-go" for the math lesson! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Harald Fuchs <[EMAIL PROTECTED] To: [EMAIL PROTECTED] .net> cc: Sent by: news Fax to: <[EMAIL PROTECTED] Subject: Re: need help with a complicated join rg> 05/26/2004 06:00 AM Please respond to hf517 In article <[EMAIL PROTECTED]>, Harald Fuchs <[EMAIL PROTECTED]> writes: > SELECT q.symbol, q.date, > q.quote * product (s.split_from / s.split_to) AS adjusted_quote > FROM quotes q > LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date > GROUP BY q.symbol, q.date, q.quote > ORDER BY q.symbol, q.date > The problem is how to define the 'product' aggregate (along the lines > of 'sum'). [ I like talking to myself :-) ] A workaround for the missing product aggregate would be SELECT q.symbol, q.date, q.quote, q.quote * exp(sum(log(coalesce(s.split_from/s.split_to,1)))) FROM quotes q LEFT JOIN splits s ON s.symbol = q.symbol AND s.date > q.date GROUP BY q.symbol, q.date, q.quote ORDER BY q.symbol, q.date but don't ask me how that performs... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]