Re: Consecutive row count query

On Thu, Mar 17, 2005 at 20:21:24 +0000, Leon Stringer <leon.stringer@ntlworld.com> 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.