title: SELECT TOP 1 MAX(DATETIME)
body: <p>Suppose I have a table called <code>'@DeviceSample'</code> and another table called <code>'@DevicesActivityDataSample'.</code></p>
  
  <p>I want To SELECT top 1 <code>'MAX(DATETIME)'</code> FROM <code>'@DevicesActivityDataSample'</code> for each Device BUT when I had Duplicated rows In 'DevicesActivityDataSample' In My result I get all Duplicated rows but I looking for top one. 
  and another problem is when I want select from 100000 rows it take long long time to get my result because of calculating MAX this is my sample code </p>
  
  <pre><code>DECLARE @DeviceSample TABLE
  (
  RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  ADeviceID INT ,
  DeviceName VARCHAR(50) NOT NULL
  ) 
  INSERT @DeviceSample
  SELECT 1,'DEVICE 1' UNION ALL
  SELECT 2,'DEVICE 2' UNION ALL
  SELECT 3,'DEVICE 3'
  
  
  
  DECLARE @DevicesActivityDataSample TABLE
  (
  RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  BDeviceID INT,
  DeviceDateTime DATETIME,
  CMD NVARCHAR(50)
  ) 
  
  INSERT @DevicesActivityDataSample
  SELECT 1, '2013-02-14 19:08:46.000','HHJJ' UNION ALL
  SELECT 2, '2013-02-15 03:30:24.000','HHJJ' UNION ALL
  SELECT 2, '2013-02-15 03:30:24.000','HHJJ' UNION ALL
  SELECT 2, '2013-02-15 03:30:24.000','HHJJ' UNION ALL
  SELECT 1, '2013-02-16 03:30:24.000','HHJJ' UNION ALL
  SELECT 1, '2013-02-11 03:30:24.000','HHJJ' UNION ALL
  SELECT 2, '2013-02-10 03:30:24.000','HHJJ' UNION ALL
  SELECT 3, '2013-02-07 04:25:24.000','HHJJ' UNION ALL
  SELECT 3, '2013-02-12 23:02:02.000','HHJJ' 
  
  
  
      SELECT  *
      FROM        @DeviceSample
      INNER JOIN  
      @DevicesActivityDataSample 
              ON  ADeviceID = BDeviceID
      WHERE       DeviceDateTime IN (
                      SELECT    MAX(DeviceDateTime) AS MaxDate
                      FROM      @DevicesActivityDataSample                                
                      GROUP BY  BDeviceID
                  ) 
  
  GO
  </code></pre>
  
  <p>and How can I get fast fast fast  result for 1000000 rows or more rows</p>