Re: Howto get a group_number like row_number for groups

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