Matrixkid

Hi there, I'm looking to get the AVG's of multiple rows, multiple times. I want to get the AVG(price) from the last 10 rows, 20 rows, 50 rows, 100 rows, 200 rows Is there a way to do this without having to use multiple SELECTS? I was thinking maybe theres a way to pull the results using LIMIT 200, and then for each of the AVG's use that result set? Simple table setup. ID - Big Int,20 Price - Decimal 10,2 Entry_Time - Timestamp Thanks, MK

Perfect. Definitely was the table structure. Works like it should now. Thanks for the guidance.

Yeah, as I hit the button it occurred to me that it might be better to get rid of the option_id field and use the primary key as: ID,Symbol,Expiration,Strike, Category But the reason behind my current key is that option_id represents all of the above fields except for ID, and the ID field is not necessarily unique since some entries are at the exact same time. What would you suggest as the key? Here is some test data: INSERT INTO `chains` (`id`, `symbol`, `expiration`, `strike`, `category`, `last`, `volume`, `openint`, `stock_price`) ' VALUES ('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'C', '0.00', 0, 0, '127.88'), ('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'P', '0.02', 834, 510, '127.88'), ('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'C', '0.02', 981, 0, '127.99'), ('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'P', '0.03', 1098, 510, '127.99');

I'm having an issue with a SELECT statement. I find that if I have a single row of data as the result and using ORDER BY it slows it right down. As soon as I either remove the ORDER BY or add another row of data it returns it in reasonable time. My data returned for the most part will have more than 1 result (hence the LIMIT 2) but there are some cases where there is only an individual row and it's stalling things. Profiling Status Time starting 0.000082 checking permissions 0.000007 Opening tables 0.000020 System lock 0.000016 init 0.000043 optimizing 0.000021 statistics 0.000016 preparing 0.000016 executing 0.000004 Sorting result 0.000005 Sending data 15.276259 end 0.000013 query end 0.056590 closing tables 0.000030 freeing items 0.000156 logging slow query 0.000008 logging slow query 0.000007 cleaning up 0.000009 Showing rows 0 - 0 (1 total, Query took 15.3337 sec) SELECT expiration, strike, category, last, volume, stock_price FROM chains WHERE option_id = 'AMZN^^120203P00165000' AND DATE( id ) = DATE( NOW( ) ) AND TIME( id ) BETWEEN '09:30:00' AND '16:00:00' ORDER BY id DESC LIMIT 2 As soon as I insert a new row with the same option_id value and a different id(timestamp) value the results is this: Showing rows 0 - 1 (2 total, Query took 0.2272 sec) OR if I remove the ORDER BY id DESC it returns a similar timed result, less than a second: Showing rows 0 - 0 (1 total, Query took 0.5024 sec) The SQL EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE chains index NULL PRIMARY 27 NULL 2 Using where Lastly, The table structure: CREATE TABLE IF NOT EXISTS `chains` ( `id` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `option_id` varchar(21) NOT NULL, `symbol` char(4) NOT NULL, `expiration` date NOT NULL, `strike` decimal(10,2) NOT NULL, `category` char(1) NOT NULL, `last` decimal(10,2) NOT NULL, `volume` int(50) NOT NULL, `openint` int(50) NOT NULL, `stock_price` decimal(12,2) NOT NULL, `stock_volume` int(20) NOT NULL, PRIMARY KEY (`id`,`option_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; In the end, im trying to grab the "latest" two results. Thanks ahead of time for the help! It's much appreciated!

Hi there, I have a table as outlined below and I'm trying to get the MAX and MIN values for a specific Id but only if the MAX entry date is greater than the MIN entry date. table: ID ENTRY_DATE NAME PRICE 1 2012-01-23 16:09:35 MONKEY 99.33 2 2012-01-23 11:04:09 MONKEY 97.65 3 2012-01-23 09:31:19 MONKEY 93.05 4 2012-01-23 15:12:14 DICE 30.49 5 2012-01-23 12:01:24 DICE 32.00 6 2012-01-23 08:01:24 DICE 31.00 So what im trying to do is grab the MAX(price) and MIN(price) when the MAX(price) entry_date is greater than the MIN(price) entry_date. From the table above, my result would be: NAME MaxPrice MinPrice PriceDiff MONKEY 99.33 93.05 6.28 'Dice' would not show up because the MAX date/time happens before the MIN. I have tried a LEFT JOIN but I cant seem to figure it out. Here is what I have right now, but it obviously grabs all results as I cant seem to figure out how to compare the entry dates... SELECT name, MAX(price) as MaxPrice, MIN(price) as MinPrice, MAX(price)-MIN(price) AS PriceDiff, FROM products WHERE DATE(entry_date) = DATE(NOW()) GROUP BY name ORDER BY PriceDiff DESC Thanks for the help!

Perfect. Thanks!

Im currently using load to make the request. I believe load has callback functionality. Thanks!

Hey Rajiv, That didnt work. The element, input.calendarSelectDate doesnt exist upon page load, and therefore is bound to nothing. It only exists upon an ajax request. I hope that helps clarify it. Thanks MK

Hi there, Im running into an issue with the jquery ui datepicker plugin. I have it working and functioning correctly, but I need to change the way it operates. here is my current code: $("input.calendarSelectDate").live('click',function(){ $(this).datepicker({inline: true, showButtonPanel: true, buttonImage: '/cal.gif', buttonImageOnly: false, showButtonPanel: true, showOn:'both'}).datepicker('show'); }); the problem is is that the input field is loaded from an ajax call. and so the button doesnt appear next to the input box until the input field has been clicked. Is there a way to display the button before the click action is made? What about loading the the datepicker function with another event? Is there a way to bind it after the ajax call, so its almost like it runs on document.ready, with just $('#inputfield').datepicker(); - without the 'click' action binding it? Thanks for the help. MK

Oh I see, i have to replace the bracket and the double quote. Thanks for the help, its much appreciated!

yep, here it is: $html = '[ "\x3cdiv class=\"medium-content-below-item\"\x3e\n \x3cdiv class=\"desc\"\x3e\n \x3ch3 class=\"medium-content-below-item-title\"\x3e\x3ca class=\"title_link\" href=\"music/albums/1633820\"\x3e\n \x3cspan \x3eEmpire State Of Mind\x3c/span\x3e\n \x3c/a\x3e\x3c/h3\x3e\n \n \x3ca href=\"music/artists/195154/jay-z\"\x3e\n \x3cdiv class=\"medium-content-below-item-subtitle\"\x3eJay-z\x3c/div\x3e\n \x3c/a\x3e\n \n \x3cdiv class=\"medium-content-below-item-time\"\x3e10:32pm\x3c/div\x3e\n \n \n \x3c/div\x3e\n\x3c/div\x3e\n" ]'; $string = str_replace("]","",$html); $string = str_replace("[","",$string); echo strip_tags(html_entity_decode($string));

For me it shoots out the exact same thing. Looks like its the square brackets messing things up. thanks MK

Hey guys Ive been trying for a couple hours now to parse a data string but Im having no luck. I have the following: [ "\x3cdiv class=\"medium-content-below-item\"\x3e\n \x3cdiv class=\"desc\"\x3e\n \x3ch3 class=\"medium-content-below-item-title\"\x3e\x3ca class=\"title_link\" href=\"music/albums/1633820\"\x3e\n \x3cspan \x3eEmpire State Of Mind\x3c/span\x3e\n \x3c/a\x3e\x3c/h3\x3e\n \n \x3ca href=\"music/artists/195154/jay-z\"\x3e\n \x3cdiv class=\"medium-content-below-item-subtitle\"\x3eJay-z\x3c/div\x3e\n \x3c/a\x3e\n \n \x3cdiv class=\"medium-content-below-item-time\"\x3e10:32pm\x3c/div\x3e\n \n \n \x3c/div\x3e\n\x3c/div\x3e\n" ] Ive tried preg_replace, str_replace and nothing works. if i try using ">" or "<" it doesnt replace, nor does "\x3c" or "\x3e" In the end, im looking to just replace everything so im left with Empire State of Mind - Jay-z - 10:32pm If someone could help me just getting a str_replace or something like that to work that would be great. Thanks MK

thanks for the help Zanus, I appreciate it. Unfortunately the revision doesnt work either. I have to remove the ) as songCheck part as it returns an error. This statement returns no results: SELECT * FROM TESTDB WHERE song NOT IN ( SELECT DISTINCT song FROM TESTDB WHERE weeknum <= 49 AND yearnum <= 2009 ) This statement returns the correct result: SELECT * FROM TESTDB WHERE song NOT IN ( SELECT DISTINCT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009 ) I can run the NOT IN on the TESTDB and get the results i need, but as soon as I apply the query to my active db the query lags and goes nowhere. The database has over 190,000 records and grows every 3 hours. heres the error: [Err] 1053 - Server shutdown in progress Im not really sure where to go from here. Thanks again. MK

Affected rows: 0 Time: 0.032ms Nope. Should return 1 row, JVC (from the table in the first post). If I change NOT EXISTS to EXISTS, it gives me the opposite results. All the songs played prior to week 49. But as soon as I make it NOT then it returns nothing.