matchoo Posted November 9, 2007 Share Posted November 9, 2007 Hey. Never claimed to be a SQL expert, so here we are... first the query, then the problem. It's a database about video clips. Query looks like this... SELECT engagementIndex,uniques FROM ts_clip_beacon_day LEFT JOIN ts_clip_view_day ON ts_clip_beacon_day.clipXid = ts_clip_view_day.clipXid WHERE ts_clip_view_day.clipXid <> '+' AND ts_clip_view_day.year = 2007 AND ts_clip_view_day.dayofyear = 304 ORDER BY engagementIndex DESC LIMIT 200 So, this works, but it's not really what I want. This gives me the top 200 by engagement. What I want is the top 200 by UNIQUES which is in ts_clip_view_day but sorted (ORDERED) by engagementIndex which is in ts_clip_beacon_day I'm sure it's simple... but I'm having brain farts. Anyone... anyone... bueller? Quote Link to comment https://forums.phpfreaks.com/topic/76673-help-with-a-subquery-join-order-by-problem/ Share on other sites More sharing options...
mezise Posted November 9, 2007 Share Posted November 9, 2007 Hi, I guess that your "clip_beacon" table may have more than one "clip_view". You are selecting from "clip_beacon", so you will get no more than one random "clip_view" for a given "clip_beacon". Try to replace FROM and JOIN tables to select from ts_clip_view_day and let me know if a result is like you want. Michal Quote Link to comment https://forums.phpfreaks.com/topic/76673-help-with-a-subquery-join-order-by-problem/#findComment-388260 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.