Thread: Selecting latest value
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
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 >
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 > > >
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 > > > > > > >
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 wheret.userid=table.userid group by userid);
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
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
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
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).
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).
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 regardsCarl van Tast
Does anyone where there is a web site or ftp site where i can download the latest release of PostgreSQL? I have been trying to use the ftp://ftp.postgresql.org but it doesnt work properly. Many thanks in advance Miguel
Hi - http://www.us.postgresql.org/sites.html has a whole list of them... On Mon, 1 Oct 2001, [iso-8859-1] Miguel Gonz�lez wrote: > Does anyone where there is a web site or ftp site where i can download the > latest release of PostgreSQL? I have been trying to use the > ftp://ftp.postgresql.org but it doesnt work properly. > > Many thanks in advance > > Miguel > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >