X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BD0D256A36 for ; Thu, 17 Mar 2005 20:59:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 71120-01 for ; Thu, 17 Mar 2005 20:59:48 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id A6DCB56A44 for ; Thu, 17 Mar 2005 20:59:46 +0000 (GMT) Received: (qmail 3357 invoked by uid 500); 17 Mar 2005 21:15:59 -0000 Date: Thu, 17 Mar 2005 15:15:59 -0600 From: Bruno Wolff III To: Leon Stringer Cc: pgsql-sql@postgresql.org Subject: Re: Consecutive row count query Message-ID: <20050317211559.GB2074@wolff.to> Mail-Followup-To: Bruno Wolff III , Leon Stringer , pgsql-sql@postgresql.org References: <1111090884.22783.5.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1111090884.22783.5.camel@localhost.localdomain> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/248 X-Sequence-Number: 20727 On Thu, Mar 17, 2005 at 20:21:24 +0000, Leon Stringer wrote: > Hi, > > I wondered if anyone could answer the following question: > > If I have a table such as the one below: > > col1 col_order > ----------- > Apple 1 > Apple 2 > Orange 3 > Banana 4 > Apple 5 > > Is there a way I can get the following results: > > Apple 2 > Orange 1 > Banana 1 > Apple 1 > > i.e. Each row is printed ordered by col_order but consecutive > appearances of the same col1 result in only a single line in the result > with the number of consecutive appearances. Which col_order value do you expect to use in ordering? You can probably join a grouped version of the table to get the counts with a distinct on version of the table to do what you want.