On Mon, Apr 12, 2010 at 12:22 PM, A B <gentosaker@gmail.com> wrote:
> Hello!
>
> I have a table (think of it as a table of log messages)
>
> time | message
> -----------------------
> 1 | a
> 2 | b
> 3 | b
> 4 | b
> 5 | a
>
> the three 'b' are the same message, so I would like to write a query
> that would give me a result that is similar to what the unix command
> "uniq -c" would give:
>
> first | message | last | count
> --------------------------------------
> 1 | a | 1 | 1
> 2 | b | 4 | 3 <--- here it squeezes
> similar consecutive messages into a single row
> 5 | a | 5 | 1
>
> How do I write such a command?
Pretty straight ahead:
select min(t), message, max(t), count(*) from table group by message.