title: Return detail data related to the corresponding MAX(datetime) value
body: <p>I have the following dataset using SQL Server 2005:</p>
  
  <pre><code>TABLE NAME: LOG
  
  TYPE       TIMESTAMP            USERID
  Job        26/03/2013 00:24     DED
  Job        21/03/2013 02:31     EGA
  Sales      NULL                 NULL
  Sales      NULL                 NULL
  </code></pre>
  
  <p>I would like to retrieve the columns TYPE, TIMESTAMP &amp; USERID based on the MAX(timestamp).. i.e the result should be </p>
  
  <pre><code>Job        26/03/2013 00:24     DED
  </code></pre>
  
  <p>I've tried the following</p>
  
  <pre><code>SELECT max(timestamp), UserID
  FROM log
  GROUP BY UserID
  </code></pre>
  
  <p>however that returns all rows.</p>
  
  <p>When I try</p>
  
  <pre><code>SELECT max(timestamp), max(UserID)
  FROM log
  GROUP BY UserID
  </code></pre>
  
  <p>that returns</p>
  
  <pre><code>Job        26/03/2013 00:24     EGA
  </code></pre>
  
  <p>I understand the max(userID) being returned is the max column value sorted alphabetically.</p>
  
  <p>Lastly when i try </p>
  
  <pre><code>SELECT * 
  FROM log 
  WHERE timestamp = (SELECTmax(timestamp) FROM log)
  </code></pre>
  
  <p>i get no result (empty)..</p>
  
  <p>If anyone can help that would be greatly appreciated!</p>
  
  <hr>
  
  <p><strong>EDIT UPDATE:</strong></p>
  
  <p>If its of any help- i should mention I have multiple tables and would and need to <code>SELECT</code> the <code>USERID</code> with the <code>MAX(timestamp)</code>WHERE TYPE` can equal either ('Job' or 'Sales').. </p>
  
  <p>I am finding this works perfectly:</p>
  
  <pre><code>SELECT Type,
         MAX(CASE WHEN log.Type IN ('Job', 'Sales') THEN log.Timestamp ELSE NULL END) As Timestamp,
  FROM log
  </code></pre>
  
  <p>The problem now is when i try:</p>
  
  <pre><code>SELECT Type,
         MAX(CASE WHEN log.Type IN ('Job', 'Sales') THEN log.Timestamp ELSE NULL END) As Timestamp,
         MAX(CASE WHEN log.Type IN ('Job', 'Sales') THEN log.UserID ELSE NULL END) As UserID,
  FROM log
  </code></pre>
  
  <p>it gives me the max(userID) based on alphabetical order, not based on the max(timeframe)..</p>
  
  <p>I need the MAX(timeframe) AND corresponding UserID when TYPE = JOB or SALES</p>
  
  <p><strong>(to place at the end of a big select query with many other columns).</strong></p>
  
  <hr>
  
  <p><strong>SAMPLE QUERY UPDATE:</strong></p>
  
  <p>here is the query i am trying to perform.. am terribly sorry for my lack of indepth knowledge!</p>
  
  <pre><code>DECLARE @FROMDATE AS DATETIME, @TODATE AS DATETIME;
  SET @FROMDATE = '1 Mar 2013'; SET @TODATE = '31 Mar 2013';
  
  SELECT CASE WHEN country.country_code IN (61,64,673,60,65,679,66,62,92,84,63,91) THEN 'APAC'
              WHEN country.country_code IN (81,82,852,853,886) THEN 'EAST ASIA'
              WHEN country.country_code IN (44,353,32,45,358,33,49,30,39,352,356,47,351,34,46,31) THEN 'EUROPE'
              WHEN country.country_code IN (1,101) THEN 'USA/CANADA' ELSE 'OTHER'
         END AS REGION,
  
         info.Status as STATUS, info.Code as CODE,
         CONVERT(VARCHAR, info.Start_Date,103) as 'START DATE', 
  
         log.Type AS TYPE,
         MAX(CASE WHEN log.Type IN ('Job','Sales') THEN log.Timestamp ELSE NULL END) As TIMESTAMP,   -- this works fine! :)
         MAX(CASE WHEN log.Type IN ('Job','Sales') THEN log.UserID ELSE NULL END) As USERID  --having trouble with this! :(
  
  FROM log JOIN info ON (log.id = info.id), country
  
  WHERE info.date BETWEEN @FROMDATE AND @TODATE
        AND info.Status NOT LIKE '%NA%'
  
  GROUP BY country.code, info.status, info.code, info.start_date, log.type
  </code></pre>
  
  <p>i hope that helps guys and thank you again for the AMAZING support. I am guessing this is something simple but i'm missing something..</p>