From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Having difficulty writing a "best-fit" query..
Date: Tue, 16 Oct 2007 17:46:33 +0100
Message-ID: <b0a4f3350710160946y4a31a227sbffbf085cdbfa487@mail.gmail.com> (raw)
Hi list,
I have a many-to-many relationship between movies and genres and, in the
link table I have a third field called which orders the "appropriateness" of
the relations within each movie.
For example:
movie_id, genre_id, relevance (i've used movie/genre titles for clarity
here, but in reality they're id's)
--------------------------------------------
beverly hills cop, action, 2
beverly hills cop, comedy, 1
the heartbreak kid, comedy, 2
the heartbreak kid, romance, 1
The above would mean, to my application:
"Beverly Hills Cop is both an Action and a Comedy movie but primarily an
Action movie."
"The Heartbreak Kid is both a Comedy and a Romance movie but primarily a
Comedy movie."
First of all, if there's a better way to model this kind of ranking/ordering
of many-to-many relationships, please let me know.
Now, to my problem..
I'm taking a subset of all my genres, and I want to get ONE row for each
movie in the subset alongside its most appropriate genre (whichever has the
highest relevance). In other words, the best fit.
I've had a few goes at writing this query but I can't seem to get it right..
The theory in my mind is to:
1. filter the link table down to rows that fit the subset of categories
(easy)
2. filter the link table further to keep only the max(relevance) for each
movie_id .. this is where i'm having trouble.
If someone can shed some light on this for me, I'd really appreciate it.
Thanks for your time,
Jamie
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: Having difficulty writing a "best-fit" query..
In-Reply-To: <b0a4f3350710160946y4a31a227sbffbf085cdbfa487@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox