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.