Subject: Consecutive row count query
From: Leon Stringer <leon.stringer@ntlworld.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Date: Thu, 17 Mar 2005 20:21:24 +0000
Message-Id: <1111090884.22783.5.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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.

Obviously I could store the table as:

col1   col_order col_count
--------------------------
Apple  1         2
Orange 2         1
Banana 3         1
Apple  4         1

But since (in my intended table) most rows will have col_count = 1, this
seems like unnecessary normalization (and semantically "wrong").

Thanks in advance for any help,

Leon Stringer