PRODUCED DISTINCT RECORD WITH MULTIPLE FIELDS

Im trying to figure out how i could pull out the data out of this table:

Table: Status

OrderNo   Status   Date        User
1          1       2011-07-01  user1
1          2       2011-07-05  user1
2          1       2011-07-01  user1
2          2       2011-07-06  user2
2          3       2011-07-10  user1

and i need to produce the following result:

OrderNo   Status   Date        User
1          2       2011-07-05  user1
2          3       2011-07-10  user1

开发者_运维问答Actually, what i needed is to display DISTINCT list of OrderNo and thier last status.

Thanks for all the possible help.

Cheers!

WITH S AS
(
    SELECT OrderNo, Status, Date, User
        , ROW_NUMBER() OVER
          (
              PARTITION BY OrderNo
              ORDER BY Date DESC
          ) Ranking
    FROM Status
)
SELECT OrderNo, Status, Date, User
FROM S
WHERE Ranking = 1

select OrderNo, max(Status) as 'Status'
from Status
group by OrderNo