pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: 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>
2001-09-20 15:25 ` Re: Selecting latest value II Chris Ruprecht <chrup999@yahoo.com>
2001-09-20 16:30 ` Re: Selecting latest value II Thurstan R. McDougle <trmcdougle@my-deja.com>
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
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>
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
1970-01-01 00:00 Re: Selecting latest value II Haller Christoph <ch@rodos.fzk.de>
@ 2001-09-20 16:30 ` Thurstan R. McDougle <trmcdougle@my-deja.com>
2001-09-20 19:47 ` Re: Selecting latest value II Carl van Tast <vanTast@Pivot.at>
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
1970-01-01 00:00 Re: Selecting latest value II Haller Christoph <ch@rodos.fzk.de>
2001-09-20 16:30 ` Re: Selecting latest value II Thurstan R. McDougle <trmcdougle@my-deja.com>
@ 2001-09-20 19:47 ` Carl van Tast <vanTast@Pivot.at>
2001-09-21 16:31 ` Re: Selecting latest value II 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
1970-01-01 00:00 Re: Selecting latest value II Haller Christoph <ch@rodos.fzk.de>
2001-09-20 16:30 ` Re: Selecting latest value II Thurstan R. McDougle <trmcdougle@my-deja.com>
2001-09-20 19:47 ` Re: Selecting latest value II Carl van Tast <vanTast@Pivot.at>
@ 2001-09-21 16:31 ` Thurstan R. McDougle <trmcdougle@my-deja.com>
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