[SQL] Consecutive row count query

[Leon Stringer]

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



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Consecutive row count query

[Leon Stringer]

> I wondered if anyone could answer the following question:

Thanks for your responses, I think I'll just add the extra column in as
Greg suggests. (BTW: I mean "unnecessary *denormalization*" which I hope
is less odd!).

Leon...


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings