Help me with this tricky join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help me with this tricky join

Author

Topic

JayToo
Starting Member

2 Posts

Posted - 2010-03-19 : 16:42:26

Hi,

I'm somewhat new to SQL so I need a bit of help with this problem (rather quick actually, so I would really appreciate if sombody would be so kind and help me out).
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:

user_id date
User1 20010101
User1 20000101
User1 19990101
User1 19970101
User2 ...
...

for User1, and the "selection" is

User1 19990101

I want this to become:

User1 19990101 19970101

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

baja_yu
Starting Member

24 Posts

Posted - 2010-03-19 : 18:30:07

Can you elaborate a bit on what the data represents and how you collect it in "selection" table? I'm asking because I thought you are keeping the latest/highest date in it and you wanted to add a new field to keep the one before it. Also you said "selection" holds the second largest (you wrote "19990101") while in fact it is the third largest of the data you gave ("20010101", "20000101", "19990101").

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:06:45

Are you using SQL 2005?
Also you're telling that you're selecting 2nd largest value for each user but in example what you've selected is 19990101 which is not second largest but its second from bottom or second smallest. is this intentional?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JayToo
Starting Member

2 Posts

Posted - 2010-03-20 : 05:16:42

Sorry for the confusion, you are correct about the error I made. The "selection" should be:

User1 20000101

and consequently:

User1 20000101 19990101

I'm using Postgre 8.2 with GP (which is unable to execute subqueries...)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-20 : 05:44:25

quote:
Originally posted by JayToo

I'm using Postgre 8.2 with GP (which is unable to execute subqueries...)

You should post in a Postgre forum. SQLTeam is for MS SQL Server

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources