Re: Selecting latest value II - Mailing list 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.useridAND 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).