sql - T-SQL : Sorting by date, 然后分组？

Question

假设我有一个如下所示的数据库表:

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

是否可以通过 start_date 降序对其进行排序，然后将每个城市按最新的 start_date 分组？例如:

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

谢谢您的回复。

Answer 1

在 SQL Server 2005 或更新版本中可能是这样的:

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