pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: 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