pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHowto get a group_number like row_number for groups
3+ messages / 3 participants
[nested] [flat]
* Howto get a group_number like row_number for groups
@ 2010-04-08 19:51 Andreas <maps.on@gmx.net>
0 siblings, 2 replies; 3+ messages in thread
From: Andreas @ 2010-04-08 19:51 UTC (permalink / raw)
To: 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 :)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Howto get a group_number like row_number for groups
@ 2010-04-09 03:41 Jaime Casanova <jcasanov@systemguards.com.ec>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: Jaime Casanova @ 2010-04-09 03:41 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Thu, Apr 8, 2010 at 2:51 PM, Andreas <maps.on@gmx.net> wrote:
> or rank() over (order by object, ts) % 2
use dense_rank() instead
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Howto get a group_number like row_number for groups
@ 2010-04-12 14:19 msi77 <msi77@yandex.ru>
parent: Andreas <maps.on@gmx.net>
1 sibling, 0 replies; 3+ messages in thread
From: msi77 @ 2010-04-12 14:19 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
Hi,
dense_rank() over (order by object) % 2
> 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 :)
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-04-12 14:19 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-08 19:51 Howto get a group_number like row_number for groups Andreas <maps.on@gmx.net>
2010-04-09 03:41 ` Re: Howto get a group_number like row_number for groups Jaime Casanova <jcasanov@systemguards.com.ec>
2010-04-12 14:19 ` Re: Howto get a group_number like row_number for groups msi77 <msi77@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox