Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    3

    Unanswered: 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:

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

    for User1, and the "selection" is

    User1 20000101

    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
    Last edited by JayToo; 03-20-10 at 06:08.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    What version of Postgres?

  3. #3
    Join Date
    Mar 2010
    Posts
    3
    I'm using: 8.2 with Greenplum (which won't support subqueries, btw)

  4. #4
    Join Date
    Mar 2010
    Posts
    3
    Solved it with help from another forum.
    If anybody is interested:

    SELECT x.id,x.date,y.date date_previous
    FROM
    ( SELECT t1.id
    , t1.date
    , COUNT(*) rank
    FROM master t1
    JOIN master t2
    ON t2.id = t1.id
    AND t2.date >= t1.date
    GROUP
    BY id,date
    ) x
    JOIN
    ( SELECT t1.id
    , t1.date
    , COUNT(*) rank
    FROM master t1
    JOIN master t2
    ON t2.id = t1.id
    AND t2.date >= t1.date
    GROUP
    BY id,date
    ) y
    ON y.id = x.id
    AND y.rank = x.rank+1
    WHERE x.rank = 2;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •