Help with a subquery join order by problem

[matchoo]

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?

[mezise]

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