From: migurus@yahoo.com   
      
   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.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)