pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Selecting latest value
6+ messages / 4 participants
[nested] [flat]
* Re: Selecting latest value
@ 1970-01-01 00:00 Haller Christoph <ch@rodos.fzk.de>
parent: Patrik Kudo <kudo@partitur.se>
0 siblings, 1 reply; 6+ messages in thread
From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
To: kudo@partitur.se; +Cc: pgsql-sql
Try
create NEWtable (userid text, val integer, ts timestamp);
insert into NEWtable
select userid, val, max(ts) from table group by userid, val;
Regards, Christoph
>
> Hi,
>
> I have a table which basically looks like this:
>
> create table (userid text, val integer, ts timestamp);
>
> This table holds multiple values for users, timestamped for history
> reasons.
>
> Now I need to fetch the latest val for each userid to insert into a new
> table (with about the same schema, except for uniqueness on userid).
> I belive this should be a trivial task, but I'm experience total lack of
> insight here...
>
> Comments?
>
> /Patrik Kudo
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Selecting latest value
@ 2001-09-18 16:22 Patrik Kudo <kudo@partitur.se>
0 siblings, 1 reply; 6+ messages in thread
From: Patrik Kudo @ 2001-09-18 16:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have a table which basically looks like this:
create table (userid text, val integer, ts timestamp);
This table holds multiple values for users, timestamped for history
reasons.
Now I need to fetch the latest val for each userid to insert into a new
table (with about the same schema, except for uniqueness on userid).
I belive this should be a trivial task, but I'm experience total lack of
insight here...
Comments?
/Patrik Kudo
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting latest value
@ 2001-09-20 13:57 Patrik Kudo <kudo@partitur.se>
parent: Haller Christoph <ch@rodos.fzk.de>
0 siblings, 2 replies; 6+ messages in thread
From: Patrik Kudo @ 2001-09-20 13:57 UTC (permalink / raw)
To: Haller Christoph <ch@rodos.fzk.de>; +Cc: pgsql-sql
On Thu, 20 Sep 2001, Haller Christoph wrote:
> Try
> create NEWtable (userid text, val integer, ts timestamp);
> insert into NEWtable
> select userid, val, max(ts) from table group by userid, val;
That won't work. That will give me multiple userid-val combinations. Sure,
the userid-val combinations will be unique, but I want unique userids
with only the latest val for each userid.
/Patrik Kudo
> >
> > Hi,
> >
> > I have a table which basically looks like this:
> >
> > create table (userid text, val integer, ts timestamp);
> >
> > This table holds multiple values for users, timestamped for history
> > reasons.
> >
> > Now I need to fetch the latest val for each userid to insert into a new
> > table (with about the same schema, except for uniqueness on userid).
> > I belive this should be a trivial task, but I'm experience total lack of
> > insight here...
> >
> > Comments?
> >
> > /Patrik Kudo
> >
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting latest value
@ 2001-09-20 14:49 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Patrik Kudo <kudo@partitur.se>
1 sibling, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2001-09-20 14:49 UTC (permalink / raw)
To: Patrik Kudo <kudo@partitur.se>; +Cc: Haller Christoph <ch@rodos.fzk.de>; pgsql-sql
On Thu, 20 Sep 2001, Patrik Kudo wrote:
> On Thu, 20 Sep 2001, Haller Christoph wrote:
>
> > Try
> > create NEWtable (userid text, val integer, ts timestamp);
> > insert into NEWtable
> > select userid, val, max(ts) from table group by userid, val;
>
> That won't work. That will give me multiple userid-val combinations. Sure,
> the userid-val combinations will be unique, but I want unique userids
> with only the latest val for each userid.
Maybe something like: (assuming that ts is unique within each userid,
otherwise what does latest mean? I haven't tried this, so it probably
doesn't parse, but...)
select userid, val from table where ts=(select max(ts) from table t where
t.userid=table.userid group by userid);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting latest value
@ 2001-09-20 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Patrik Kudo <kudo@partitur.se>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-09-20 15:07 UTC (permalink / raw)
To: Patrik Kudo <kudo@partitur.se>; +Cc: Haller Christoph <ch@rodos.fzk.de>; pgsql-sql
Patrik Kudo <kudo@partitur.se> writes:
> create table (userid text, val integer, ts timestamp);
> This table holds multiple values for users, timestamped for history
> reasons.
>
> Now I need to fetch the latest val for each userid to insert into a new
> table (with about the same schema, except for uniqueness on userid).
> I belive this should be a trivial task, but I'm experience total lack of
> insight here...
This is what SELECT DISTINCT ON was invented for. I don't know any
comparably easy way to do it in standard SQL, but with DISTINCT ON
it's not hard:
SELECT DISTINCT ON (userid) userid, val, ts FROM table
ORDER BY userid, ts DESC;
See the DISTINCT ON example in the SELECT reference page for more info:
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-select.html
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Selecting latest value
@ 2001-09-20 15:12 Patrik Kudo <kudo@partitur.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Patrik Kudo @ 2001-09-20 15:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Haller Christoph <ch@rodos.fzk.de>; pgsql-sql
On Thu, 20 Sep 2001, Tom Lane wrote:
> This is what SELECT DISTINCT ON was invented for. I don't know any
> comparably easy way to do it in standard SQL, but with DISTINCT ON
> it's not hard:
>
> SELECT DISTINCT ON (userid) userid, val, ts FROM table
> ORDER BY userid, ts DESC;
>
> See the DISTINCT ON example in the SELECT reference page for more info:
> http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-select.html
Thanks!
I was trying some stuff with distinct on, but in combination with max(ts),
which obviously didn't work. Now it works much better =)
/Patrik Kudo
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-09-20 15:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-18 16:22 Selecting latest value Patrik Kudo <kudo@partitur.se>
1970-01-01 00:00 ` Haller Christoph <ch@rodos.fzk.de>
2001-09-20 13:57 ` Patrik Kudo <kudo@partitur.se>
2001-09-20 14:49 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-09-20 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-09-20 15:12 ` Patrik Kudo <kudo@partitur.se>
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