Need some help with a query (uniq -c)

From: A B <gentosaker(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Need some help with a query (uniq -c)
Date: 2010-04-12 18:22:44
Message-ID: k2gdbbf25901004121122wbd6daeccpe671b7a31e7be083@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

I could of course write a plpgsql function that loops but that is not
as interesting as finding out if this can be done in a single simple
command.

Perhaps it would be best to regularly delete neighbouring similar
rows and keeping a "count" value would reduce the number of rows and
make it more efficient if the query would be run many times and the
number of duplicate messages would be large.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Mead 2010-04-12 18:30:29 Re: Lifekeeper
Previous Message Jorge Arevalo 2010-04-12 18:12:38 Showing debug messages in my C function