X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 039B64760E2 for ; Mon, 24 Feb 2003 12:26:12 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 15414-08 for ; Mon, 24 Feb 2003 12:26:00 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 3F15A475CB1 for ; Mon, 24 Feb 2003 12:02:57 -0500 (EST) Received: (qmail 11168 invoked by uid 500); 24 Feb 2003 17:16:33 -0000 Date: Mon, 24 Feb 2003 11:16:33 -0600 From: Bruno Wolff III To: Eddie Cheung Cc: pgsql-sql@postgresql.org Subject: Re: Help with query involving aggregation and joining. Message-ID: <20030224171633.GA11072@wolff.to> Mail-Followup-To: Eddie Cheung , pgsql-sql@postgresql.org References: <20030223204542.GA6337@wolff.to> <20030224051738.74417.qmail@web80410.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030224051738.74417.qmail@web80410.mail.yahoo.com> User-Agent: Mutt/1.3.25i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/533 X-Sequence-Number: 11931 On Sun, Feb 23, 2003 at 21:17:38 -0800, Eddie Cheung wrote: > > 2) Bruno suggested the following query: > > select distinct on (course.courseid) > history.id, course.courseid, course.name, > history.submission > from course natural join history > order by course.courseid, history.submission desc; > > I have not used NATURAL JOIN before, but from what I > know, it joins the columns with the same name. Since > the joining columns of History and Course have > different names, I have replace JOIN clause. Please > let me know if I have made a mistake. No I made a mistake and thought that courseId was used in both places. > The modified query is: > SELECT DISTINCT ON (course.id) course.id, > history.id, course.name, history.submission > FROM history JOIN course ON history.courseId = > course.id > ORDER BY course.id, history.submission desc; > > The results returned are : > id | id | name | submission > -----+----+-----------+------------ > 101 | 1 | Physics | 2002-01-20 > 102 | 4 | Chemistry | 2002-02-22 > 104 | 3 | Maths | 2002-04-30 > > The problem here is that the results are not ordered > by the submission date. If I sort by > "history.submission" first, I get > ERROR: SELECT DISTINCT ON expressions must match > initial ORDER BY expressions. > Please note that I cannot select distinct on the > course.name either because it is NOT unique. The > original tables are much larger, and the only unique > column is the id. My suggestion could be modified by making it a subselect and adding another order by clause. You might get different performance (worse or better) than the updated version of Josh's solution.