Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F1D009FB826 for ; Tue, 16 Oct 2007 14:11:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 40523-03 for ; Tue, 16 Oct 2007 14:11:10 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.224]) by postgresql.org (Postfix) with ESMTP id 92FFC9FB6F4 for ; Tue, 16 Oct 2007 14:11:17 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 68so1238640wri for ; Tue, 16 Oct 2007 10:11:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=m03FOY0Vp8/R5jCfmRrTMLM0hsA5zg+Y0P6YO4++33E=; b=i+a0J/egG2r3BsSG6YPoxIsUNXV9cdHt+MTFYklpSI0wfFwON/5qgtT+OWMXUT0Jt2E76zmN/cEyrQiBo9ZjSG0vTXZ6czWgEMGe8FTP+w0WXIxwc/2+ZNoYagRI5LjVse1Sse2ihN7bCFMWbbTXdhPLjfnhrMgAx4vi472Gsmc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Mh8nnPgz61UcIlm+azpv7foKZWHYj3cw1LtQudY5ifXNzvfm4OCpWKvom/P1KSMnssOyL8Bc+BJuXFJECrp1G5I10O8y+BO4yu29R/d0ZpAl6E4+5s7jUlBG5M4rdsrQT8asMACTkI3+wAopz/YlpMlKyKvey7rxta2Dd5J2KsQ= Received: by 10.90.91.14 with SMTP id o14mr11039994agb.1192554676925; Tue, 16 Oct 2007 10:11:16 -0700 (PDT) Received: by 10.90.117.8 with HTTP; Tue, 16 Oct 2007 10:11:16 -0700 (PDT) Message-ID: Date: Tue, 16 Oct 2007 12:11:16 -0500 From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" To: "Jamie Tufnell" Subject: Re: Having difficulty writing a "best-fit" query.. Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/71 X-Sequence-Number: 29443 On 10/16/07, Jamie Tufnell wrote: > 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. You didn't provide the expected output, but try: SELECT * FROM t tt WHERE relevance = (SELECT MAX(relevance) FROM t WHERE movie_id = tt.movie_id)