Обсуждение: Howto get a group_number like row_number for groups
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 :)
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
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