pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Number timestamped rows 3+ messages / 3 participants [nested] [flat]
* Number timestamped rows @ 2011-11-02 16:36 Jan Peters <petersjan@gmx.at> 2011-11-02 16:51 ` Re: Number timestamped rows Andreas Kretschmer <akretschmer@spamfence.net> 2011-11-08 09:36 ` Re: Number timestamped rows Jasen Betts <jasen@xnet.co.nz> 0 siblings, 2 replies; 3+ messages in thread From: Jan Peters @ 2011-11-02 16:36 UTC (permalink / raw) To: pgsql-sql Dear all, maybe a stupid question, but: I have a table that is ordered like this: user_id|timestamp|event 1 |0:1 |event_a 1 |0:2 |event_b 2 |0:1 |event_b 2 |0:3 |event_c 2 |0:4 |event_b 3 |0:1 |event_a and I would like to number them according to their timestamps like this: user_id|timestamp|event |order 1 |0:1 |event_a |1 1 |0:2 |event_b |2 2 |0:1 |event_b |1 2 |0:3 |event_c |2 2 |0:4 |event_b |3 3 |0:1 |event_a |1 How would I do this with an UPDATE statement (e.g.) in pgsql? Thanks in advance for any hint Jan -- NEU: FreePhone - 0ct/min Handyspartarif mit Geld-zurück-Garantie! Jetzt informieren: http://www.gmx.net/de/go/freephone ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Number timestamped rows 2011-11-02 16:36 Number timestamped rows Jan Peters <petersjan@gmx.at> @ 2011-11-02 16:51 ` Andreas Kretschmer <akretschmer@spamfence.net> 1 sibling, 0 replies; 3+ messages in thread From: Andreas Kretschmer @ 2011-11-02 16:51 UTC (permalink / raw) To: pgsql-sql Jan Peters <petersjan@gmx.at> wrote: > Dear all, > maybe a stupid question, but: I have a table that is ordered like this: > > user_id|timestamp|event > 1 |0:1 |event_a > 1 |0:2 |event_b > 2 |0:1 |event_b > 2 |0:3 |event_c > 2 |0:4 |event_b > 3 |0:1 |event_a > > and I would like to number them according to their timestamps like this: > > user_id|timestamp|event |order > 1 |0:1 |event_a |1 > 1 |0:2 |event_b |2 > 2 |0:1 |event_b |1 > 2 |0:3 |event_c |2 > 2 |0:4 |event_b |3 > 3 |0:1 |event_a |1 > untested: select user_id, timestamp, event, row_number() over (partition by user_id order by timestamp) as "order" from your_table > How would I do this with an UPDATE statement (e.g.) in pgsql? I think, you don't need an UPDATE, just a SELECT. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Number timestamped rows 2011-11-02 16:36 Number timestamped rows Jan Peters <petersjan@gmx.at> @ 2011-11-08 09:36 ` Jasen Betts <jasen@xnet.co.nz> 1 sibling, 0 replies; 3+ messages in thread From: Jasen Betts @ 2011-11-08 09:36 UTC (permalink / raw) To: pgsql-sql On 2011-11-02, Jan Peters <petersjan@gmx.at> wrote: > Dear all, > maybe a stupid question, but: I have a table that is ordered like this: > Tables aren't ordered. Sometimes they may seem to be ordered, but they seldom stay that way for long. > and I would like to number them according to their timestamps like this: > How would I do this with an UPDATE statement (e.g.) in pgsql? If you want ordered data use an ORDER BY clause in the select, there is no other reliable way. -- ⚂⚃ 100% natural ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-11-08 09:36 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-11-02 16:36 Number timestamped rows Jan Peters <petersjan@gmx.at> 2011-11-02 16:51 ` Andreas Kretschmer <akretschmer@spamfence.net> 2011-11-08 09:36 ` Jasen Betts <jasen@xnet.co.nz>
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