Hi list,<br /><br />I have a many-to-many relationship between movies and genres and, in the link table I have a third
fieldcalled which orders the "appropriateness" of the relations within each movie. <br /><br />For example: <br /><br
/>movie_id,genre_id, relevance (i've used movie/genre titles for clarity here, but in reality they're id's)<br
/>--------------------------------------------<br/>beverly hills cop, action, 2<br />beverly hills cop, comedy, 1 <br
/>theheartbreak kid, comedy, 2<br />the heartbreak kid, romance, 1<br /><br />The above would mean, to my
application:<br/>"Beverly Hills Cop is both an Action and a Comedy movie but primarily an Action movie."<br /> "The
HeartbreakKid is both a Comedy and a Romance movie but primarily a Comedy movie."<br /><br />First of all, if there's a
betterway to model this kind of ranking/ordering of many-to-many relationships, please let me know.<br /><br />Now, to
myproblem..<br /><br />I'm taking a subset of all my genres, and I want to get ONE row for each movie in the subset
alongsideits most appropriate genre (whichever has the highest relevance). In other words, the best fit. <br /><br
/>I'vehad a few goes at writing this query but I can't seem to get it right.. The theory in my mind is to:<br /><br
/>1.filter the link table down to rows that fit the subset of categories (easy)<br /><br />2. filter the link table
furtherto keep only the max(relevance) for each movie_id .. this is where i'm having trouble. <br /><br />If someone
canshed some light on this for me, I'd really appreciate it.<br /><br />Thanks for your time,<br />Jamie<br />