Re: Consecutive row count query

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