Consecutive row count query - Mailing list pgsql-sql

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