Re: Need some help with a query (uniq -c)

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.