Data typing calculation results in SELECT?
I'm doing a select where I lag across records, and would like to compute some differences. It seems to do the calcs right if I evaluate the result in an IF statement, but if I just want to get the calculation result stored, it seems to default to a data type that only stores on digit. Here's the offending code: create table rfdata SELECT t1.*, dayname( t1.tradedate ) AS tradedayofweek, dayofmonth( t1.tradedate ) AS trademonthday, dayofyear( t1.tradedate ) AS tradedoy, monthname( t1.tradedate ) AS trademonth, ((t2.currentPrice - t1.currentPrice) / t1.currentPrice) AS d1closechange, ((t2.dayMinPrice - t1.currentPrice) / t1.currentPrice) AS d1lowchange, ((t2.dayMaxPrice - t1.currentPrice) / t1.currentPrice) AS d1highchange, if(((t2.dayMaxPrice-t1.currentPrice)/t1.currentPrice).03,1,0) AS d1threepcthit, ((t3.open - t1.currentPrice) / t1.currentPrice) AS d2openchange, if(((t3.open-t1.currentPrice)/t1.currentPrice).02,1,0) AS d2twopct FROM sorted_data AS t1 LEFT JOIN sorted_data AS t2 ON t2.newid = t1.newid +1 AND t2.symbol = t1.symbol LEFT JOIN sorted_data AS t3 ON t3.newid = t1.newid +2 AND t3.symbol = t1.symbol; This give me a able with the following offending results: +--+---+ | d1highchange | d1threepcthit | +--+---+ | 0.0 | 0 | | 0.0 | 1 | | 0.0 | 0 | Where d1highchange should have been a decimal that was over .03, given the 1 in the second column. If I look at the 1st column in phpMyAdmin, it appears to be type Double, with 25,1 in the defaults/format display. Any way I can rewrite my table creation code to be sure that value gets stored out several decimal places? Thanks, I looked in the manual but didn't see anything about column typing in a CREATE ... SELECT query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT into OUTFILE... Can I add column names?
I've scoured the online documentation, and I have a good SELECT into OUTFILE query that gives me what I want... Except that there is no 1st row with column names. Hoping I missed something obvious... Any pointers on this one? -- David L. Van Brunt, Ph.D. Outlier Consulting Development mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT into OUTFILE... Can I add column names?
Okay, so I didn't miss anything... Good to know! Any way I CAN create an output file with those names? Maybe if I make a table first? On 4/6/04 21:43, Paul DuBois [EMAIL PROTECTED] wrote: At 20:43 -0500 4/6/04, David L. Van Brunt, Ph.D. wrote: I've scoured the online documentation, and I have a good SELECT into OUTFILE query that gives me what I want... Except that there is no 1st row with column names. Hoping I missed something obvious... Any pointers on this one? You can't do it with SELECT ... INTO OUTFILE. -- David L. Van Brunt, Ph.D. Outlier Consulting Development mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT into OUTFILE... Can I add column names?
FABULOUS! It works like a charm, even on a very complex series of commands. Redirected output into a file, and viola! Thanks a bunch! On 4/6/04 22:17, Paul DuBois [EMAIL PROTECTED] wrote: At 22:04 -0500 4/6/04, David L. Van Brunt, Ph.D. wrote: Okay, so I didn't miss anything... Good to know! Any way I CAN create an output file with those names? Maybe if I make a table first? One way, if tab-delimited output is satisfactory, is to just run mysql in batch mode. In interactive mode, you get this kind of output: mysql select 1, 2, 3; +---+---+---+ | 1 | 2 | 3 | +---+---+---+ | 1 | 2 | 3 | +---+---+---+ 1 row in set (0.05 sec) In batch mode, you get this kind of output: % echo select 1, 2, 3 | mysql 1 2 3 1 2 3 The first row is the column names. The following rows will be the data. Those are tabs between the columns. On 4/6/04 21:43, Paul DuBois [EMAIL PROTECTED] wrote: At 20:43 -0500 4/6/04, David L. Van Brunt, Ph.D. wrote: I've scoured the online documentation, and I have a good SELECT into OUTFILE query that gives me what I want... Except that there is no 1st row with column names. Hoping I missed something obvious... Any pointers on this one? You can't do it with SELECT ... INTO OUTFILE. -- David L. Van Brunt, Ph.D. Outlier Consulting Development mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- David L. Van Brunt, Ph.D. Outlier Consulting Development mailto: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I grab the value of a row index with the field values of the same row?
I¹ve got large data set. Each day I load in 8000 stocks, info about how they did that day. So the primary key, an auto incrementing value, numbers them in order. But I¹d like to sort by Stock ticker, then pull the value from the next day¹s price to stick at the end of a row for the current day¹s information. I got some help the other day on how to lag across records using the primary key and a self join, but after a sort the primary keys are not really what I¹m after I want tomorrow¹s price for the same stock (next record in a sorted dataset) not the next value of the primary key (which is for a different stock). I¹m stumped. I thought if I made an index on ³ticker² and ³date² combined, that I could use that index for the lag and I¹d be pretty close. But a) I might be very wrong about that, and b) can¹t find any documentation on how to call up that index¹s value as part of a SELECT¹s record set. Any ideas? Am I close? -- David L. Van Brunt, Ph.D. Outlier Consulting Development mailto: [EMAIL PROTECTED]
Record lag functionality in MySQL?
I have a table of data... A1 B1 C1 A2 B2 C2 And I¹d like to make a query where I ³lag² a value, grabbing a value from the next row of the table. Most stat packages have a lag function, but I can¹t find this in MySQL. The end result would look like: A1 B1 C1 D1 (where ³D1² would equal A2 from above) Any suggestions? -- David L. Van Brunt, Ph.D. Outlier Consulting Development mailto: [EMAIL PROTECTED]
