title: T-SQL: Sorting by date, then grouping?
body: <p>Let's say I have a database table that looks like this:</p>
  
  <pre><code>ID          name       salary      start_date              city       region
  ----------- ---------- ----------- ----------------------- ---------- ------
            1 Jason            40420 1994-02-01 00:00:00.000 New York   W
            2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
            3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
            4 Linda            40620 1997-11-04 00:00:00.000 New York   N
            5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
            6 James            70060 1999-09-06 00:00:00.000 Toronto    N
            7 Alison           90620 2000-08-07 00:00:00.000 New York   W
            8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
            9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
  </code></pre>
  
  <p>Is there an easy to sort this by start_date descending, then for the each city group them by the latest start_date?  For example:</p>
  
  <pre><code>ID          name       salary      start_date              city       region
  ----------- ---------- ----------- ----------------------- ---------- ------
        9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
        6 James            70060 1999-09-06 00:00:00.000 Toronto    N
        3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
        8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
        5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
        2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
        7 Alison           90620 2000-08-07 00:00:00.000 New York   W
        4 Linda            40620 1997-11-04 00:00:00.000 New York   N
        1 Jason            40420 1994-02-01 00:00:00.000 New York   W
  </code></pre>
  
  <p>Thank you for your replies.</p>