Thread: Help me with this tricky join
Hi, I'm somewhat new to SQL so I need a bit of help with this problem. So I have 2 tables: "selection" and "master", both have two columns in each: "user_id" and "date". The "selection" contains one row for each "user_id" and depicts _one_ "date" value for each user. The "master" contains all "date" changes for each "user_id". I.e., there are many dates for each "user_id". It is a history of previous occurrences. Now, I want to add a 3rd column to the "selection" table that is the "date" value from one step back for each "user_id". I.e., if the "master" contains: User1 20010101 User1 20000101 User1 19990101 User1 19970101 for User1, and the "selection" is User1 19990101 I want this to become: User1 20000101 19990101 How do I do this? A simple join wont do it since it is dependent on what value "date" is for each user.. Moreover, I know for a fact that the "date" in "selection" is the second largest value -> I want to add the 3rd largest value from "master" for each user. BR, Jay
CORRECTION: The original "selection" should be: User1 20000101 NOT User1 19990101
Jay <josip.2000@gmail.com> wrote: > Hi, > > I'm somewhat new to SQL so I need a bit of help with this problem. So > I have 2 tables: "selection" and "master", both have two columns in > each: "user_id" and "date". > > The "selection" contains one row for each "user_id" and depicts _one_ > "date" value for each user. > The "master" contains all "date" changes for each "user_id". I.e., > there are many dates for each "user_id". It is a history of previous > occurrences. > > Now, I want to add a 3rd column to the "selection" table that is the > "date" value from one step back for each "user_id". I.e., if the > "master" contains: > > User1 20010101 > User1 20000101 > User1 19990101 > User1 19970101 > > for User1, and the "selection" is > > User1 19990101 > > I want this to become: > > User1 20000101 19990101 > > How do I do this? A simple join wont do it since it is dependent on > what value "date" is for each user.. I think, you don't need a new column, because you can determine this value (assuming you have 8.4) test=*# select * from selection ; user_id | date ---------+---------- user1 | 20010101 user1 | 20000101 user1 | 19990101 user1 | 19970101 (4 Zeilen) Zeit: 0,255 ms test=*# select *, lag(date) over (order by date)from selection order by date desc; user_id | date | lag ---------+----------+---------- user1 | 20010101 | 20000101 user1 | 20000101 | 19990101 user1 | 19990101 | 19970101 user1 | 19970101 | (4 Zeilen) Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Thanks! But, since the master can contain many users (user2, user3, and so on) I suppose this won't be a proper solution? Sorry if I was a bit unclear in my description. I.e., the master is of the form: user_id date User1 20010101 User1 20000101 User1 19990101 User1 19970101 User2 ... ... Btw, I'm using Postgre version 8.2 and I cannot use subqueries do the GP software.
In response to Jay : > Thanks! > But, since the master can contain many users (user2, user3, and so on) > I suppose this won't be a proper solution? > Sorry if I was a bit unclear in my description. > > I.e., the master is of the form: > > user_id date > User1 20010101 > User1 20000101 > User1 19990101 > User1 19970101 > User2 ... > ... That's not the problem ... > > Btw, I'm using Postgre version 8.2 and I cannot use subqueries do the but this. lag() over () and similar windowing functions new since 8.4. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99