Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: SQL Query help - do I JOIN or APPLY?

    Hi All- consider the following scenario with some mocked up data:

    TableA
    User_ID, Name
    1, John
    2, Sally
    3, Mary

    TableB
    User_ID, Status_ID, Status_Updated_Date
    1, 60, 2011-01-01
    1, 61, 2011-01-03
    1, 62, 2011-01-15
    2, 60, 2011-02-10
    2, 61, 2011-02-16
    2, 62, 2011-02-21
    3, 60, 2011-03-07
    3, 61, 2011-03-10
    3, 62, 2011-03-13

    What I want to do is to join TableA on TableB (two times I believe), and have data that appears on TableB on two separate rows appear in my query output on a single row...specifically the rows for each User that have the greatest and second greatest 'Status_Updated_Date':

    TableB
    User_ID, Status_ID, Status_Updated_Date
    1, 60, 2011-01-01
    1, 61, 2011-01-03
    1, 62, 2011-01-15

    2, 60, 2011-02-10
    2, 61, 2011-02-16
    2, 62, 2011-02-21

    3, 60, 2011-03-07
    3, 61, 2011-03-10
    3, 62, 2011-03-13


    Given that, if I want the record with the greatest 'Status_Updated_Date' for a particular User ID on TableB, I can do so by querying:
    Code:
    SELECT DISTINCT TOP 1 *
    FROM TableB WHERE User_ID = 2
    ORDER BY Status_Updated_Date DESC
    And would get back:
    User_ID, Status_ID, Status_Updated_Date
    2, 62, 2011-02-21


    Likewise, if I want the record with the second greatest 'Status Updated Date' for a particular User ID on TableB, I can do so querying:
    Code:
    SELECT TOP 1 *
    FROM (SELECT DISTINCT TOP 2 *
    FROM TableB
    WHERE User_ID = 2
    ORDER BY Status_Updated_Date DESC) AS a
    ORDER BY Status_Updated_Date
    And would get back:
    User_ID, Status_ID, Status_Updated_Date
    2, 61, 2011-02-16


    Where I'm stuck is how to construct my WHERE syntax to make the following happen on a single output row:

    TableA.User_ID, TableA.User, TableB.Status_ID (2nd greatest), TableB.Status_ID (greatest), TableB.Status_Updated_Date (greatest)
    1, John, 61, 62, 2011-01-15
    2, Sally, 61, 62, 2011-02-21
    3, Mary, 61, 62, 2011-03-13


    This way, I can determine when the most recent Status change took place, what the Status previously was, and what the Status is now.

    Thanks in advance to everyone who can assist!


    -Matthew
    Last edited by thephotomatt; 09-28-11 at 22:22.

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    The simple solution is to use correlated subqueries. Something like.

    SELECT TableA.Name,
    TableA.User_ID,
    [...]
    (SELECT DISTINCT TOP 1 *
    FROM TableB WHERE User_ID = 2 and TableA.User_ID = TableB.User_ID
    ORDER BY Status_Updated_Date DESC)
    [...]
    FROM TableA

    (May slow down performance...)


    Note that TOP is vendor specific SQL (not ANSI compliant).

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ROW_NUMBER() windowed table function was included in ANSI SQL:2003,
    and it was implemented on many commercial RDBMS(e.g. Oracle, DB2, MS SQL Server).

    See
    Overview of SQL:2003
    SQL:2003 Has Been Published

    Example 1:
    Note: Tested on DB2 9.7.5 for Windows.
    Code:
    WITH
    /**************************************************
    **********  Start of sample/test data.   **********
    **************************************************/
      TableA
    ( User_ID , Name ) AS (
    VALUES
      ( 1 , 'John'  )
    , ( 2 , 'Sally' )
    , ( 3 , 'Mary'  )
    )
    , TableB
    ( User_ID , Status_ID , Status_Updated_Date ) AS (
    VALUES
      ( 1, 60 , '2011-01-01' )
    , ( 1, 61 , '2011-01-03' )
    , ( 1, 62 , '2011-01-15' )
    , ( 2, 60 , '2011-02-10' )
    , ( 2, 61 , '2011-02-16' )
    , ( 2, 62 , '2011-02-21' )
    , ( 3, 60 , '2011-03-07' )
    , ( 3, 61 , '2011-03-10' )
    , ( 3, 62 , '2011-03-13' )
    )
    /**************************************************
    **********    End of sample/test data.   **********
    **************************************************/
    SELECT a.User_ID
         , a.Name
         , MAX( CASE rnum
                WHEN 2 THEN
                     b.Status_ID
                END
              ) AS Status_ID_2nd
         , MAX( CASE rnum
                WHEN 1 THEN
                     b.Status_ID
                END
              ) AS Status_ID_max
         , MAX( b .Status_Updated_Date
              ) AS Status_Updated_Date
     FROM  TableA AS a
     INNER JOIN (
           SELECT b.*
                , ROW_NUMBER()
                     OVER( PARTITION BY User_ID
                               ORDER BY Status_Updated_Date DESC
                         ) AS rnum
            FROM  TableB AS b
           ) AS b
       ON  b.User_ID =  a.User_ID
       AND b.rnum    <= 2
     GROUP BY
           a.User_ID
         , a.Name
     ORDER BY
           a.User_ID
    ;
    ------------------------------------------------------------------------------
    
    USER_ID     NAME  STATUS_ID_2ND STATUS_ID_MAX STATUS_UPDATED_DATE
    ----------- ----- ------------- ------------- -------------------
              1 John             61            62 2011-01-15         
              2 Sally            61            62 2011-02-21         
              3 Mary             61            62 2011-03-13         
    
      3 record(s) selected.
    Last edited by tonkuma; 02-05-12 at 11:19.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Tested on Mimer SQL Developers - Mimer SQL-2003 Validator

    Comments got error.
    Code:
    Result:
    
    WITH 
    /************************************************** 
    ^-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    syntax error: /*******************************... <end>
      correction: <identifier> AS ( SELECT * FROM <identifier> ) SELECT * FROM
                  <identifier> <end>

    I removed comments.

    The SQL-2003 Validator!

    Enter your SQL statement(s) in the box below and simply click the "Test SQL" button:

    Code:
    WITH
      TableA
    ( User_ID , Name ) AS (
    VALUES
      ( 1 , 'John'  )
    , ( 2 , 'Sally' )
    , ( 3 , 'Mary'  )
    )
    , TableB
    ( User_ID , Status_ID , Status_Updated_Date ) AS (
    VALUES
      ( 1, 60 , '2011-01-01' )
    , ( 1, 61 , '2011-01-03' )
    , ( 1, 62 , '2011-01-15' )
    , ( 2, 60 , '2011-02-10' )
    , ( 2, 61 , '2011-02-16' )
    , ( 2, 62 , '2011-02-21' )
    , ( 3, 60 , '2011-03-07' )
    , ( 3, 61 , '2011-03-10' )
    , ( 3, 62 , '2011-03-13' )
    )
    SELECT a.User_ID
         , a.Name
         , MAX( CASE rnum
                WHEN 2 THEN
                     b.Status_ID
                END
              ) AS Status_ID_2nd
         , MAX( CASE rnum
                WHEN 1 THEN
                     b.Status_ID
                END
              ) AS Status_ID_max
         , MAX( b .Status_Updated_Date
              ) AS Status_Updated_Date
     FROM  TableA AS a
     INNER JOIN (
           SELECT b.*
                , ROW_NUMBER()
                     OVER( PARTITION BY User_ID
                               ORDER BY Status_Updated_Date DESC
                         ) AS rnum
            FROM  TableB AS b
           ) AS b
       ON  b.User_ID =  a.User_ID
       AND b.rnum    <= 2
     GROUP BY
           a.User_ID
         , a.Name
     ORDER BY
           a.User_ID
    ;

    Code:
     
    Result:
    
    The following features outside Core SQL-2003 are used:
    
    F641, "Row and table constructors"
    F391, "Long identifiers"
    T611, "Elementary OLAP operations"
    F591, "Derived tables"
    T121, "WITH (excluding RECURSIVE) in query expression"
    F661, "Simple tables"
    
    The following vendor reserved word is used:
    
    NAME

Posting Permissions

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