SQL Query help - do I JOIN or APPLY?

thephotomatt · #1 (**permalink**) 09-28-11, 19:46

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

JarlH · #2 (**permalink**) 09-29-11, 02:42

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).

tonkuma · #3 (**permalink**) 02-05-12, 10:05

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.

tonkuma · #4 (**permalink**) 02-07-12, 08:26

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

Internet Services

Web Development

Digital Marketing

Consumer Tech