How to query for records based on most recent version
You can use ROW_NUMBER() and OVER clause to "group" (partition) the rows per ordernumber, then order them by date (descending) and keep only the first rows (WHERE rn = 1):

WITH cte AS
  ( SELECT  id, ordernumber, [date], status,
            rn = ROW_NUMBER() OVER (PARTITION BY ordernumber
                                    ORDER BY date DESC)
    FROM    tableX
  )
SELECT  id, ordernumber, [date], status,
FROM    cte
WHERE   rn = 1
  AND   status = 'active' ;


And I really do hope that the date column is a DATE column and not a CHAR one. Otherwise the above ORDER BY will not work as you expect.



After the last, edit, it seems that the date column is actually of DATE dataype, which is good.

But really, you should't use '1-3-04' format for input. For output it may be fine (the users of the application may like that dubious format) but for input, no. Because you can never be sure if the date that will actually be stored in the database will be 2004-01-03 or 2004-03-01 or 2001-03-04 (or some other.)

Use a format that is unambiguous, like '2004-01-03' (the ISO YYYY-MM-DD format) or even better (for SQL-Server specifically) the YYYYMMDD format: '20040103'. See the blog by Aaron Bertrand: Bad habits to kick : mis-handling date / range queries