Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Recent content by jeff5311
-
Weighted Random Select
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... -
Weighted Random Select
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 -
Weighted Random Select
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 -
Weighted Random Select
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 -
Weighted Random Select
Thanks for your help!!! Unfortunately, MYSQL tells me this isn't valid syntax and won't run the query. Any other suggestions??? Help!!! -
Weighted Random Select
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()"... -
Another MySQL Join Question
Oh no! I have version 4.0.15 of MySQL. Is there another way to write the query without a sub-select? Thanks!!! Jeff -
Another MySQL Join Question
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... -
Another MySQL Join Question
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... -
MySQL JOIN Question
Thanks for your help guys! r397 - that's exactly what I was looking for... it works perfectly. Thanks Again! Jeff -
MySQL JOIN Question
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... -
Can I Write this Query without a sub SELECT?
ericbrunson, your SQL worked great! you are a lifesaver! thanks so much for your help. jeff -
Can I Write this Query without a sub SELECT?
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! -
Can I Write this Query without a sub SELECT?
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... -
Can I Write this Query without a sub SELECT?
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...
