pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Selecting latest value II
5+ messages / 4 participants
[nested] [flat]

* Re: Selecting latest value II
@ 1970-01-01 00:00  Haller Christoph <ch@rodos.fzk.de>
  0 siblings, 2 replies; 5+ messages in thread

From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
  To: kudo@partitur.se; +Cc: pgsql-sql

What do you mean by 
"the latest val for each userid" 
I cannot understand how a value of type integer 
can have a attribute like "latest". 
Sorry, but I need at least a bit more information. 
Regards, Christoph 
> 
> 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] 5+ messages in thread

* Re: Selecting latest value II
@ 2001-09-20 15:25  Chris Ruprecht <chrup999@yahoo.com>
  parent: Haller Christoph <ch@rodos.fzk.de>
  1 sibling, 0 replies; 5+ messages in thread

From: Chris Ruprecht @ 2001-09-20 15:25 UTC (permalink / raw)
  To: pgsql-sql

what about using 'distinct' in you select statement?

----- Original Message -----
From: "Haller Christoph" <ch@rodos.fzk.de>
To: "Patrik Kudo" <kudo@partitur.se>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, September 20, 2001 11:02 AM
Subject: Re: [SQL] Selecting latest value II


> What do you mean by
> "the latest val for each userid"
> I cannot understand how a value of type integer
> can have a attribute like "latest".
> Sorry, but I need at least a bit more information.
> Regards, Christoph
> >
> > 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
> > > >
> > >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Selecting latest value II
@ 2001-09-20 16:30  Thurstan R. McDougle <trmcdougle@my-deja.com>
  parent: Haller Christoph <ch@rodos.fzk.de>
  1 sibling, 1 reply; 5+ messages in thread

From: Thurstan R. McDougle @ 2001-09-20 16:30 UTC (permalink / raw)
  To: pgsql-sql

Look at his table structure, you will see a timestamp.  His request can
be rephrased as "The val field from the latest record for each userid in
turn.

Carl van Tast had 2 good methods as follows 

SELECT userid, val
FROM tbl
WHERE NOT EXISTS (SELECT * FROM tbl AS t2
                  WHERE tbl.userid=t2.userid AND t2.ts > tbl.ts);

or

SELECT tbl.userid, tbl.val
FROM tbl
     INNER JOIN
     (SELECT userid, max(ts) AS maxts
      FROM tbl
      GROUP BY userid) AS t2
     ON (tbl.userid=t2.userid AND tbl.ts=t2.maxts);

A third method is

SELECT tbl.userid, tbl.val FROM tbl 
WHERE (tbl.ts=
(SELECT ts FROM tbl AS t2 WHERE t2.userid=tbl.userid AND t2.ts>=tbl.ts
ORDER BY userid, ts DESC LIMIT 1)
ORDER BY userid
);

His INNER JOIN method forms a result set from the inner select then
selects the specifically required records from outer level (from the
table).  This can be a good way if there are lots of entries per userid.

His EXISTS method looks for any higher timestamps (on the same userid)
for each record in turn, if there are none then this is the highest
timestamp record for that userid.


Another method is to implement a FIRST() aggregate function...look
though the list archives for a discussion on doing this.

Haller Christoph wrote:
> 
> What do you mean by
> "the latest val for each userid"
> I cannot understand how a value of type integer
> can have a attribute like "latest".
> Sorry, but I need at least a bit more information.
> Regards, Christoph
> >
> > 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
> > > >
> > >
> >
> >
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
This is the identity that I use for NewsGroups. Email to 
this will just sit there. If you wish to email me replace
the domain with knightpiesold . co . uk (no spaces).



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Selecting latest value II
@ 2001-09-20 19:47  Carl van Tast <vanTast@Pivot.at>
  parent: Thurstan R. McDougle <trmcdougle@my-deja.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Carl van Tast @ 2001-09-20 19:47 UTC (permalink / raw)
  To: pgsql-sql

Hi, Thurstan

On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle"
<trmcdougle@my-deja.com> wrote:

> [...]
>Carl van Tast had 2 good methods as follows 
>
>SELECT userid, val
>FROM tbl
>WHERE NOT EXISTS (SELECT * FROM tbl AS t2
>                  WHERE tbl.userid=t2.userid AND t2.ts > tbl.ts);
>
>or
>
>SELECT tbl.userid, tbl.val
>FROM tbl
>     INNER JOIN
>     (SELECT userid, max(ts) AS maxts
>      FROM tbl
>      GROUP BY userid) AS t2
>     ON (tbl.userid=t2.userid AND tbl.ts=t2.maxts);

... although I like Tom Lane's even better. This one should outperform
all others, especially my first one:

> SELECT DISTINCT ON (userid) userid, val, ts FROM table
> ORDER BY userid, ts DESC;

That's the reason I'm here: learning by helping :-)

Kind regards
 Carl van Tast



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Selecting latest value II
@ 2001-09-21 16:31  Thurstan R. McDougle <trmcdougle@my-deja.com>
  parent: Carl van Tast <vanTast@Pivot.at>
  0 siblings, 0 replies; 5+ messages in thread

From: Thurstan R. McDougle @ 2001-09-21 16:31 UTC (permalink / raw)
  To: pgsql-sql

Hi back

Carl van Tast wrote:
> 
> Hi, Thurstan
> 
> On Thu, 20 Sep 2001 17:30:46 +0100, "Thurstan R. McDougle"
> <trmcdougle@my-deja.com> wrote:
> 
> > [...]
> >Carl van Tast had 2 good methods as follows
> >
> >SELECT userid, val
> >FROM tbl
> >WHERE NOT EXISTS (SELECT * FROM tbl AS t2
> >                  WHERE tbl.userid=t2.userid AND t2.ts > tbl.ts);
> >
> >or
> >
> >SELECT tbl.userid, tbl.val
> >FROM tbl
> >     INNER JOIN
> >     (SELECT userid, max(ts) AS maxts
> >      FROM tbl
> >      GROUP BY userid) AS t2
> >     ON (tbl.userid=t2.userid AND tbl.ts=t2.maxts);
> 
> ... although I like Tom Lane's even better. This one should outperform
> all others, especially my first one:
> 
> > SELECT DISTINCT ON (userid) userid, val, ts FROM table
> > ORDER BY userid, ts DESC;
Indeed.  I was looking for a 1 pass method but had not found one.  I had
thought of DISTINCT, but not DISTINCT ON as it I have not used it yet...
Even though I had just been looking at the code for the
DISTINCT/DISTINCT ON processing!

> 
> That's the reason I'm here: learning by helping :-)
Well, I think of helping more as paying my dues for the help received.

> 
> Kind regards
>  Carl van Tast

-- 
This is the identity that I use for NewsGroups. Email to 
this will just sit there. If you wish to email me replace
the domain with knightpiesold . co . uk (no spaces).



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-09-21 16:31 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 Re: Selecting latest value II Haller Christoph <ch@rodos.fzk.de>
2001-09-20 15:25 ` Chris Ruprecht <chrup999@yahoo.com>
2001-09-20 16:30 ` Thurstan R. McDougle <trmcdougle@my-deja.com>
2001-09-20 19:47   ` Carl van Tast <vanTast@Pivot.at>
2001-09-21 16:31     ` Thurstan R. McDougle <trmcdougle@my-deja.com>

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