title: How to get the last record per group in SQL
body: <p>I am facing a rather interesting problem. I have a table with the following structure:</p>
  
  <pre><code>CREATE TABLE [dbo].[Event]
  (
      Id int IDENTITY(1,1) NOT NULL,
      ApplicationId nvarchar(32) NOT NULL,
      Name nvarchar(128) NOT NULL,
      Description nvarchar(256) NULL,
      Date nvarchar(16) NOT NULL,
      Time nvarchar(16) NOT NULL,
      EventType nvarchar(16) NOT NULL,
      CONSTRAINT Event_PK PRIMARY KEY CLUSTERED ( Id ) WITH (
          PAD_INDEX = OFF, 
          STATISTICS_NORECOMPUTE = OFF, 
          IGNORE_DUP_KEY = OFF, 
          ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS  = ON
      )
  )
  </code></pre>
  
  <p>So the issue is that I have to display this data in a grid. There are two requirements. The first one is to display all events regardless of what application threw them. This is simple - a select statement will do the job very easily.</p>
  
  <p>The second requirement is to be able to group events by <code>Application</code>. In other words display all events in a way that if the <code>ApplicationId</code> is repeated more than once, grab only the last entry for every application. The primary key of the Event (Id) at this point is no longer needed in this query/view.</p>
  
  <p>You may also notice that the Event Date and Time are in string format. This is ok because they follow the standard date time formats: mm/dd/yyyy and hh:mm:ss. I can pull those as follows:</p>
  
  <pre><code>Convert( DateTime, (Date + ' ' +  Time)) AS 'TimeStamp'
  </code></pre>
  
  <p>My issue is that if I use AGGREGATE functions on the rest of the columns I don't know how would they behave:</p>
  
  <pre><code>SELECT
      ApplicationId,
      MAX(Name),
      MAX(Description),
      MAX( CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp',
      MAX( EventType )
  FROM
      Event
  GROUP BY
      ApplicationId
  </code></pre>
  
  <p>The reason I am hesitant to do so is because a function such as <code>MAX</code> will return the largest value for a given column from a (sub)set of records. It does not necessary pull the last record!</p>
  
  <p>Any ideas on how to select only the last record on a per application basis?</p>