From: reb01501@NOSPAMyahoo.com   
      
   migurus wrote:   
   > I am dealing with schema where customers have one-to-many events, so   
   > each customer has at least one event record. I need to show customer   
   > details and his most recent event.   
   >   
   > This is how I do it:   
   >   
   > declare @CUSTOMERS table (   
   > CUST_ID int NOT NULL PRIMARY KEY   
   > ,CUST_NAME varchar(64) NOT NULL   
   > ,CUST_PHONE varchar(24) NULL   
   > );   
   > declare @EVENTS table (   
   > SEQ_ID int NOT NULL IDENTITY PRIMARY KEY   
   > ,CUST_ID int NOT NULL   
   > ,EVENT_DATE date NOT NULL   
   > );   
   > insert into @CUSTOMERS(CUST_ID,CUST_NAME,CUST_PHONE) values   
   > (1000, 'DOLE,MARY', '212-409-4406'),   
   > (2000, 'DALES,KYE', '212-664-7055'),   
   > (3300, 'DOBBS,JOHN', '818-752-6028'),   
   > (4000, 'DUNES,RAY', '818-648-3890');   
   >   
   > insert into @EVENTS(CUST_ID, EVENT_DATE) values   
   > (1000, '2002-01-01'),   
   > (2000, '2002-01-01'),   
   > (3300, '2002-01-01'),   
   > (4000, '2002-01-01'),   
   > (3300, '2003-06-01'),   
   > (3300, '2008-10-15'),   
   > (1000, '2011-12-01');   
   >   
   > select distinct   
   > E.CUST_ID   
   > ,MAX(E.EVENT_DATE) over (partition by E.CUST_ID) [EVENT_DATE]   
   > ,C.CUST_NAME   
   > ,C.CUST_PHONE   
   > from   @EVENTS E   
   >  JOIN @CUSTOMERS C   
   >    ON E.CUST_ID = C.CUST_ID;   
   >   
   > Output:   
   > CUST_ID EVENT_DATE CUST_NAME CUST_PHONE   
   > 1000 2011-12-01 DOLE,MARY 212-409-4406   
   > 2000 2002-01-01 DALES,KYE 212-664-7055   
   > 3300 2008-10-15 DOBBS,JOHN 818-752-6028   
   > 4000 2002-01-01 DUNES,RAY 818-648-3890   
   >   
   > +++   
   >   
   > Does anyone have any better idea? any critique is welcome.   
      
   This query returns the most recent event per customer:   
      
   SELECT CUST_ID,MAX(EVENT_DATE) AS LatestEvent   
   FROM @EVENTS   
      
      
   Now you simply need to join these results to your @CUSTOMERS table. Here's   
   one way to do that:   
      
   WITH e AS (   
   SELECT CUST_ID,MAX(EVENT_DATE) AS LatestEvent   
   FROM @EVENTS)   
   SELECT E.CUST_ID, LatestEvent,   
   ,C.CUST_NAME   
   ,C.CUST_PHONE   
   from  E   
     JOIN @CUSTOMERS C   
       ON E.CUST_ID = C.CUST_ID;   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)