Recent content by jeff5311

I constructed a query which might return results faster, but it has all these nasty subqueries. Is there a better way to write this? SELECT * FROM ( SELECT *, ( SELECT SUM( weight ) FROM table t2 WHERE t2.id <= t1.id ) AS sum_weight FROM table t1 ) AS t3...

Thanks Tony!!! That SQL significantly dropped the load on the server... I'm not sure why (it still has the dreaded "ORDER BY RAND()" in there), but it's working great!!! thanks againg for all your help. jeff

Thanks Rudy and Tony! I'll try both methods, but I think any SQL statement with the "ORDER BY RAND()" clause in it will have poor performance... but I'm no guru, it's just what I've read. Thanks again for your help!!! Jeff

Tony, Yes you are correct!!! I want more probability of the higher-weighted records returned. Again, the above query works probability wise, it just gets slower and slower as the table grows. Does anyone know how to re-write this? Help!!! Thanks, Jeff

Thanks for your help!!! Unfortunately, MYSQL tells me this isn't valid syntax and won't run the query. Any other suggestions??? Help!!!

I'm trying to retrieve a "random" row from a table where the randomness is "weighted" by a factor, let's call it "weight" (original, huh?). the following (pseude-SQL) works but is EXTREMELY slow: SELECT * FROM table ORDER BY RAND() * ( 1 / weight) LIMIT 1; I've read that "ORDER BY RAND()"...

Oh no! I have version 4.0.15 of MySQL. Is there another way to write the query without a sub-select? Thanks!!! Jeff

Tony, Thanks for your help! That looks like it might work, but I can can't get MySQL to evaluate the sub-select. It gives me a syntax error #1064 (I'm using phpMyAdmin to run the query) as follows: MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that...

Sorry for all the questions guys! I'm a SQL newb... I have 2 tables I'm trying to join: traits trait_num trait_name trait_value 1 color blue 2 color red 3 color green 4 size small 5 size medium 6 size...

Thanks for your help guys! r397 - that's exactly what I was looking for... it works perfectly. Thanks Again! Jeff

This query I'm trying to construct seems simple, but I can't get it to work. Can someone please help?!?!?! I have two tables... My "products_to_traits" table has the following data: product_num trait_num 1 1 1 12 1 23 1 30 2 1 2 30 2...

ericbrunson, your SQL worked great! you are a lifesaver! thanks so much for your help. jeff

thanks for your help! if the table is named "categories," do you mean: select a.category_value from categories as a join categories as b using ( product_id ) where b.category_value = 'engineering' if so, i'll try that and report back. thanks again!

good question! sorry i wasn't clear to start. i want only values that all widgets with a red value have (i think!). another example from the data would be: if i selected "engineering," i would want to return "black," "red," "square," and "round," since widget1 and widget2 have all of those...

i'm trying to construct what i believe would be a fairly simple query against 1 table with just 3 columns. however, with my limited sql knowledge, it feels like i'm going in circles. can any mysql gurus please help? my table is called "categories" and has the following fields and example data...