FIRST() and LAST() are not available (yet, I'll keep hoping) but you can mirror their functionality using TEMP tables, and you could probably do it with SubQueries (4.1+). There are options out there to get around them.
The experience I've had with with temp tables has been quite good, though I've only used it on a 100,000 row table. It can get a little hairy writing the queries though. Chris -----Original Message----- From: Schulman, Michael [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 7:14 PM To: 'Fredrick Bartlett'; [EMAIL PROTECTED] Subject: RE: Time series As far as I know min(price) and max(price) will return the lowest and higest price, not the first and last in the group. Again I know first and last break the paradaigm of SQL's bucket mentality but it is crucial to doing timeseries analysis. And timeseries aggregation as the query I gave is trying to do. Thanks again, Mike -----Original Message----- From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] Sent: Thursday, January 01, 2004 10:12 PM To: Schulman, Michael; [EMAIL PROTECTED] Subject: Re: Time series Is Hour a DateTime? If so, will this work... SELECT ticker, DATE_FORMAT(Hour,'%H' ), min(price), max(price) from pricedata order by DATE_FORMAT(Hour,'%H' ) group by DATE_FORMAT(Hour,'%H' ) ----- Original Message ----- From: "Schulman, Michael" <[EMAIL PROTECTED]> To: "'Fredrick Bartlett'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 01, 2004 6:59 PM Subject: RE: Time series > That only returns one number.. what we are really looking for is something > like > > SELECT ticker, hour, first(price), last(price) from pricedata group by hour > > Sorry for the confusion. > > Thanks, > Mike > > -----Original Message----- > From: Fredrick Bartlett [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 01, 2004 9:57 PM > To: Schulman, Michael; [EMAIL PROTECTED] > Subject: Re: Time series > > > Hmmm... > First: select * from table1 order by field1 asc limit 1 > Last: select * from table1 order by field1 desc limit 1 > > > ----- Original Message ----- > From: "Schulman, Michael" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, January 01, 2004 6:47 PM > Subject: Time series > > > > Hi, > > > > I work for a large financial instituition. We are currently evaluating > > databases to store intraday stock data. These are large tables with 40 > > million rows per day. We've done some initial testing with MySQL and have > > been extremely impressed with its speed and ease of use. I know that it > > goes agains the SQL standard but adding a FIRST,LAST aggregate function > > along with some other time series functions would allow mysql to compete > > with just about any timeseries database, and open up mysql to a huge > market > > of financial firms. I know my firm would most likely purchase it. Has > > anyone developed anyhting like this as an add on? > > > > Thanks, > > Mike > > > > -------------------------------------------------------------------------- > ---- > > This message is intended only for the personal and confidential use of the > > designated recipient(s) named above. If you are not the intended > recipient of > > this message you are hereby notified that any review, dissemination, > > distribution or copying of this message is strictly prohibited. This > > communication is for information purposes only and should not be regarded > as > > an offer to sell or as a solicitation of an offer to buy any financial > > product, an official confirmation of any transaction, or as an official > > statement of Lehman Brothers. Email transmission cannot be guaranteed to > be > > secure or error-free. Therefore, we do not represent that this > information is > > complete or accurate and it should not be relied upon as such. All > > information is subject to change without notice. > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -------------------------------------------------------------------------- ---- > This message is intended only for the personal and confidential use of the > designated recipient(s) named above. If you are not the intended recipient of > this message you are hereby notified that any review, dissemination, > distribution or copying of this message is strictly prohibited. This > communication is for information purposes only and should not be regarded as > an offer to sell or as a solicitation of an offer to buy any financial > product, an official confirmation of any transaction, or as an official > statement of Lehman Brothers. Email transmission cannot be guaranteed to be > secure or error-free. Therefore, we do not represent that this information is > complete or accurate and it should not be relied upon as such. All > information is subject to change without notice. > ---------------------------------------------------------------------------- -- This message is intended only for the personal and confidential use of the designated recipient(s) named above. If you are not the intended recipient of this message you are hereby notified that any review, dissemination, distribution or copying of this message is strictly prohibited. This communication is for information purposes only and should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product, an official confirmation of any transaction, or as an official statement of Lehman Brothers. Email transmission cannot be guaranteed to be secure or error-free. Therefore, we do not represent that this information is complete or accurate and it should not be relied upon as such. All information is subject to change without notice. -- 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]
