Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: Fairly easy SQL Question

    Hello all,

    I have a table that has 3 fields - [Sub ID], [Ord Date], and [Account Manager]. The data set has multiple order dates for each sub ID, and possibly multiple account reps per each sub Id. I need to create a list that has the unique Sub ID and accompanying rep for the max order date of that ID. I know that I cannot use an aggregate function in the where clause, and that if I try to group by reps as well as sub id's it will replicate IDs on me. I've tried using sub queries, iif statements, and the having clause, but nothing is clicking.


    So far my thought is (incomplete):

    Code:
    Select Table1.[sub id], table1.[account manager], max(table1.[ord date]) as MaxOrd
    from table1
    group by table1.[sub id];
    Any suggestions?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Split the question in to two parts:
    1) list unique Sub ID for the max order date
    Code:
    SELECT sub_id
         , Max(order_date) As max_order_date
    FROM   your_table
    GROUP
        BY sub_id
    2) get the accompanying rep for the unique Sub ID and max order date

    To achieve this we can either save the above query and join to that query (e.g. A), or use it as a subquery (e.g. B)

    A)
    Code:
    SELECT x.sub_id
         , x.account_manager
         , x.order_date
         , y.sub_id
         , y.max_order_date
    FROM    your_table As x
     INNER
      JOIN the_saved_query_from_above As y
        ON x.sub_id = y.sub_id
       AND x.order_date = y.max_order_date
    B) we simply swap "the_saved_query_from_above" for the actual query, wrapped in brackets
    Code:
    SELECT x.sub_id
         , x.account_manager
         , x.order_date
         , y.sub_id
         , y.max_order_date
    FROM    your_table As x
     INNER
      JOIN (
            SELECT sub_id
                 , Max(order_date) As max_order_date
            FROM   your_table
            GROUP
                BY sub_id
           ) As y
        ON x.sub_id = y.sub_id
       AND x.order_date = y.max_order_date
    HTH
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2012
    Posts
    15
    Ah, of course! I figured it was something simple. For some reason I overlooked the date as something to join on.

    That'll do nicely. Thanks!

Posting Permissions

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