SQL Server Performance

select each record by latest date!

Discussion in 'General Developer Questions' started by Bredsox, Sep 25, 2006.

  1. Bredsox New Member


    Hi All,
    I have one table which has CustomerID, ApplicationID and DateModified. Each of these CustomerID has more than 1 records based on different ApplicationID (so one customer can have 2-3 applicationID) and DateModified. I need to get one record each of those ApplicationID associated with the given CustomerID based on latest DateModified. It looks very simple but I can't seem to figure out.

    Example:
    CustomerID ApplicationID DateModified
    1 100 9/20/06
    1 200 9/21/06
    2 500 8/20/06
    2 600 9/10/06
    2 700 9/23/06

    Output:
    CustomerID ApplicationID DateModified
    1 200 9/21/06
    2 700 9/23/06
  2. khtan New Member


    select *
    from table t
    where DateModified = (select max(DateModified) from table x where x.CustomerID = t.CustomerID)



    KH
  3. Bredsox New Member

    quote:Originally posted by khtan



    select *
    from table t
    where DateModified = (select max(DateModified) from table x where x.CustomerID = t.CustomerID)



    KH

    Thanks Khtan for the quick reply. But, I am getting more records than expected with this query. I wanted to return 155 records of latest ApplicationID based on 155 CustomerID but it's returning more than 1605 records. Am I missing something here?

    select *
    from table t
    where DateModified = (select max(DateModified) from table x where x.CustomerID = t.CustomerID And CustomerID IN (list of 155 CustomerIDs))
  4. Bredsox New Member

    I think it was data issue. The query seem to be working fine. Thanks khtan for the query.
  5. Adriaan New Member

    If you have more than one record for the client with the same DateModified, then you will get all matching rows returned, not just one.

    This version filters on both the most recent ApplicationId and the most recent DateModified:

    SELECT * FROM table t
    WHERE t.ApplicationId =
    (SELECT TOP 1 y.ApplicationId FROM table y WHERE y.CustomerID = t.CustomerID
    AND y.DateModified = (SELECT MAX(DateModified) FROM table x WHERE x.CustomerID = t.CustomerID)
    ORDER BY y.applicationId DESC)

    This is assuming that ApplicationId is a numeric column with a sequential numbering system.

Share This Page