* Ciprian Trofin > I have 2 tables: currencies and quotes > > currencies > ========== > id currency > ------------- > > quotes > ====== > id date id_currency value > ------------------------------- > Index (date, id_currency - UNIQUE) > > > In order to find the most recent value for a currency I use the following > logic: > > 1. SELECT max(date), id_currency FROM quotes GROUP BY id_currency > > 2. for each set of values in result: > SELECT C.currency, Q.value FROM quotes Q, currencies C > WHERE C.id = Q.id_currency AND > Q.date = $result[max_date] AND > Q.id_currency = $result[id_currency] > > The procedure is quite slow, because for the 2nd step I have > nnn queries > where nnn = number of rows in the 1st step. > > Could you suggest a better and faster approach ?
Maybe you can use the max-concat trick in this case? SELECT C.currency, SUBSTRING(MAX(CONCAT(Q.date,'-',Q.value)),12) as value FROM quotes Q, currencies C WHERE C.id = Q.id_currency GROUP BY C.currency <URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
