none
How do I create a query that joins two tables and returns a single entry with the most recent update?

    Question

  • Say I have the following two tables (Purchase & Customer). 

    CustomerID StoreName PurchaseDate
    1 Asheville 9/14/2010 15:49
    2 Asheville 9/12/2010 18:49
    2 Boulder 8/14/2010 12:49
    3 Boulder 9/14/2010 15:49
    2 Austin 9/20/2010 15:49

    Table 2. Customer Table

    CustoID LastName
    1 Smith
    2 Johnson
    3 Conic

    I would like a list of the name of the customer who placed the most recent purchase by store.

    Desired query result:

    Table 3. Desired query output.

    Store LastName Purchase Date
    Asheville Smith 9/14/2010 15:49
    Boulder Conic 9/14/2010 15:49
    Austin Johnson 9/20/2010 15:49

    All my queries (using Max() and joining tables in odd ways) end up with all the records being displayed:

    Table 4. Undesired output.

    Store LastName Purchase Date
    Asheville Smith 9/14/2010 15:49
    Asheville Johnson 9/12/2010 18:49
    Boulder Conic 9/14/2010 15:49
    Boulder Johnson 8/14/2010 12:49
    Austin Johnson 9/20/2010 15:49

    Can someone please shoot me straight?  I don't know if it's worth pointing out that the tables don't link up automatically when adding them in a query designer; I have to force them to be linked through using JOIN.

     

     

     

     

     

     

    Tuesday, September 21, 2010 5:20 PM

Answers

All replies

  • SELECT
    Store,
    LastName,
    MAX(PurchaseDate)
    from
    Purchase p
    left join
    Customer c
    on p.customerID = c.custoID
    group by
    Store, LastName
    
    Tuesday, September 21, 2010 5:25 PM
  • ;with cte as
    (
     select StoreName,PurchaseDate,LastName,row_number() over (partition by StoreName order by PurchaseDate desc) as rn from Purchase P
     inner join Customer C on P.CustomerId = C.CustoID
    )
    select * from cte where rn = 1
    
    • Marked as answer by Ai-hua Qiu Thursday, September 30, 2010 8:41 AM
    Tuesday, September 21, 2010 5:29 PM
  • If 2k8:

     create table Purchase (CustomerId int, StoreName varchar(50), PurchaseDate DateTime)
    insert Purchase values(1,'Asheville','9/14/2010 15:49')
    insert Purchase values(2,'Asheville','9/12/2010 18:49')
    insert Purchase values(2,'Boulder','8/14/2010 12:49')
    insert Purchase values(3,'Boulder','9/14/2010 15:49')
    insert Purchase values(2,'Austin','9/20/2010 15:49')

    create table Customer (CustomerId int, LastName varchar(50))
    insert Customer values (1,'Smith')
    insert Customer values (2,'Johnson')
    insert Customer values (3,'Conic')


    ;with cte as (
     select c.CustomerId
      , c.LastName
      , p.StoreName
      , p.PurchaseDate
      , LastPurchaseDate = ROW_NUMBER() over (partition by p.CustomerId order by p.PurchaseDate desc)
     from dbo.Customer c
     inner join dbo.Purchase p on p.CustomerId = c.CustomerId
    )
    select StoreName
     , LastName
     , PurchaseDate
    from cte
    where LastPurchaseDate = 1

     

    Tuesday, September 21, 2010 5:29 PM
  • DECLARE @Purchase TABLE (CustomerID INT,
    StoreName VARCHAR(50),PurchaseDate VARCHAR(50))
    
    DECLARE @Customer TABLE(CustoID INT,LastName VARCHAR(50))
    
    INSERT INTO @Purchase( CustomerID ,StoreName ,PurchaseDate)
    SELECT 1, 'Asheville', '9/14/2010 15:49' UNION 
    SELECT 2, 'Asheville', '9/12/2010 18:49' UNION 
    SELECT 2, 'Boulder', '8/14/2010 12:49' UNION 
    SELECT 3, 'Boulder', '9/14/2010 15:49' UNION 
    SELECT 2, 'Austin', '9/20/2010 15:49' 
    
    INSERT INTO @Customer( CustoID, LastName )
    SELECT 1 ,'Smith' UNION
    SELECT 2, 'Johnson' UNION
    SELECT 3, 'Conic' 
    ;
    WITH CTE AS 
    (SELECT *,ROW_NUMBER() OVER (PARTITION BY storename ORDER BY PurchaseDate DESC) rn
    FROM @Purchase)
    
    SELECT StoreName,LastName,PurchaseDate 
    FROM CTE c
    JOIN @Customer cus ON c.CustomerID = cus.CustoID
    WHERE rn = 1 
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Tuesday, September 21, 2010 5:40 PM
  • Take a look at these two blogs exploring this problem in details:

     

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Ai-hua Qiu Thursday, September 30, 2010 8:42 AM
    Wednesday, September 22, 2010 2:46 AM
    Moderator
  • select

    StoreName,LastName,PurchaseDate

    from

    Purchase p join Customer c

    on

    p.CustomerId = c.CustomerId

    where

    p.PurchaseDate=

    (

    select max(PurchaseDate)

    from

    Purchase pp

    where

    pp.StoreName=p.StoreName)

    order

    by 1

    • Marked as answer by Timothy M B Saturday, October 30, 2010 2:06 AM
    Wednesday, September 22, 2010 7:59 AM
  • Thanks for all the help.

    T

    Saturday, October 30, 2010 2:06 AM