T-SQL: Sorting by date, then grouping?

Question

Let's say I have a database table that looks like this:

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

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:

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

Thank you for your replies.

Answer 1

In SQL Server 2005 or newer could be like:

select 
  * 
from
  (select *,max(start_date) over(partition by city) max_date from tablename) alias
order by max_date desc, start_date desc

Answer 2

SELECT yourTable.*
  FROM yourTable INNER JOIN
      (SELECT city, MAX(start_date) AS max_city_date 
         FROM yourTable 
        GROUP BY city) max_dates
      ON yourTable.city = max_dates.city
ORDER BY max_dates.max_city_date DESC, yourTable.city, yourTable.start_date DESC

The yourTable.city in the ORDER BY clause ensures a consistent grouping by city if two cities have the same max_city_date.

Answer 3

Join the query on itself, and group by city name. Then you can use the maximum start date for a city in the ORDER BY clause.

select c1.*
from cities c1
left join cities c2 on c1.city = c2.city
group by c1.id, c1.name, c1.salary, c1.start_date, c1.city, c1.region
order by max(c2.start_date) desc, c1.city, c1.start_date desc