Sorting and ordering by two columns

问题:

I have a query which returns a result set like this:

Proj  |  release  |  releaseDt
1     |   2       |  1/2/2013
1     |   1       |  4/5/2012
2     |   1       |  [null]
3     |   1       |  22/2/2013
1     |   3       |  [null]
3     |   2       |  [null]

I need to sort this by releaseDt, but I need to have all the records for that Proj together.

After sorting, the result should be something like this:

Proj | release |   releaseDt
1    |  2      |   1/2/2013
1    |  1      |   4/5/2012
1    |  3      |   [null]
3    |  1      |   22/2/2013
3    |  2      |   [null]
2    |  1      |   [null]

How can I do this with SQL Server?

回答1:

You want to sort by the earliest release date for a project and then by the release date within a project.

You can get the earliest date using a window function, and then use that for sorting:

select t.Proj, t.release, t.releaseDt
from (select t.*, min(releasedt) over (partition by proj) as minrdt
      from t
     ) t
order by t.minrdt, t.proj, t.releaseDt

回答2:

All you need to do is sort your table twice: first by Proj, then by releaseDt:

SELECT *
FROM mytable
ORDER BY
  Proj ASC,
  releaseDt DESC

回答3:

Try

ORDER BY proj ASC, releaseDt DESC

Should do the trick!