How do I SELECT rows from a table based on a rank value

Question

I have a table with the following (simplified) structure:

Order, Status, Sequence
1000, New, 0
1000, Viewed, 1
1000, Shipped, 2
1001, New, 0
1002, New, 0
1002, Viewed, 1
1002, New, 2

I want a SELECT statement that will return the row with the max sequence number for each order. So, in the above example, I would want the resulting dataset to be:

1000, Shipped, 2
1001, New, 0
1002, New, 2

Is there a simple way to do this? I can't seem to construct a WHERE expression that can do it.

To respond to a few comments:

• There are several DMBS in use where this problem arises: MS-SQL, MySQL, Oracle and Access.
• There are so many DMBSs involved because the system being developed is consolidating data from other databases in the organization. For this reason I have no control over column names that are reserved words but I am aware of the need to escape the names.
• Many combinations of subqueries, GROUP BY, DISTINCT, MAX and COUNT were tried to no success. Since the query results always end up in Excel I have being grabbing all the rows and sorting and removing duplicates in Excel. My preference is to skip this step by having the SQL query do the work.

Answer 1

since you haven't mentioned your RDBMS, you can try this query below,

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  Order, MAX(Sequence) maxValue
            FROM tableName
            GROUP BY Order
        ) b ON a.Order = b.Order AND
                a.Sequence = b.maxValue

this works on most RDBMS.

if your RDBMS supports Window Function, you can do it like this,

SELECT  Order, Status, Sequence
FROM
    (
        SELECT  Order, Status, Sequence,
                ROW_NUMBER() OVER (PARTITION BY Order 
                                    ORDER BY Sequence DESC)
        FROM    TableName
    ) derivedTable

remember that ORDER (also SEQUENCE but not all) is a reserved keyword and must be escaped. The escaped character should depend on what RDBMS you are using.