From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Howto get a group_number like row_number for groups |
Date: | 2010-04-08 19:51:12 |
Message-ID: | 4BBE33B0.1050408@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 :)
From | Date | Subject | |
---|---|---|---|
Next Message | Jaime Casanova | 2010-04-09 03:41:10 | Re: Howto get a group_number like row_number for groups |
Previous Message | Rob Sargent | 2010-04-08 14:59:01 | Re: Table Design for Hierarchical Data |