Message-ID: <4BBE33B0.1050408@gmx.net>
Date: Thu, 08 Apr 2010 21:51:12 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Howto get a group_number  like row_number for groups
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

Hi

I'd like to have an alternating colorindex in the output of a query that 
consecutive rows that are the same within a colum the same number.
The query generates a readable output from a log-table and a few others 
that hold referenced texts.

log  (log_id  int, log_event_fk  int, object_fk  int , ts  timestamp)
events (event_id  int, event  text)
objects (object_id  int, object  text, is_active  bool)

the easy part   :)
mind the the ordering is not primarily based on the timestamp

select   log_id,  event,  object,  ts
from log
    join events  on event_id = log_event_fk
    join objects  on object_id = object_fk
where  object.is_active
order by  object,   ts

Now I'd need a dynamically generated column that alternates between 0 
and 1 so that I can later color the rows where object is the same.

row_number()  over  (order by  object,   ts)  %  2
or rank()  over  (order by  object,   ts)  %  2
produces the 0/1 alternation for rows

When I create a subselect for objects that adds the colorindex and join 
this to the log instead of objects, I get the group-color  only  if I 
omit the sorting on the timestamp.
When I order the outer select by object, ts  the colorindex gets 0 in 
every row.   :(

I'd like to get something as this
3,  up,  dev3,  2010-4-2  10:00,       0
8,  down,  dev3,  2010-4-2  14:00,       0
9,  down,  dev3,  2010-4-2  15:00,       0
1,  up,  dev7,  2010-4-2  09:00,       1
5,  down,  dev7,  2010-4-2  17:00,       1
2,  up,  dev11,  2010-4-2  12:00,       0
7,  down,  dev11,  2010-4-2  13:00,       0
.
.

regards   :)