select the max record from table

I have a table which contains Order_id, hour, location_code. Each record corresponds to an order ID, the location it is placed and the time it was registered in that location, such as the sample as follows:

ORDER_ID |            HOUR       |LOCATION_CODE
10910568 | 1899-12-30 04:25:40   |   168
10910568 | 1899-12-30 00:44:04   |   96
10910568 | 1899-12-30 04:18:00   |  217
10910568 | 1899-12-30 04:25:41   |    6
10910569 | 1899-12-30 04:25:40   |  168
10910569 | 1899-12-30 00:44:04   |   96
10910569 | 1899-12-30 04:18:00   |  217
10910569 | 1899-12-30 04:25:42   |    7

The expect result I need is:

ORDER_ID |       HOUR           |LOCATION_CODE
10910568 | 1899-12-30 04:25:41  |      6
10910569 | 1899-12-30 04:25:42  |      7

The original table is very large so I need to extract this information for every ORDER_ID.

How can I accomplish that?

Solution

If the table is very large I suggest that you get the max HOUR for each ORDER_ID in a query with a simple GROUP BY ORDER_ID and join to the table:

select t.*
from tablename t inner join (
  select order_id, max(hour) maxhour
  from tablename
  group by order_id
) g on g.order_id = t.order_id and g.maxhour = t.hour

See the demo.
Results:

| ORDER_ID | HOUR                | LOCATION_CODE |
| -------- | ------------------- | ------------- |
| 10910568 | 1899-12-30 04:25:41 | 6             |
| 10910569 | 1899-12-30 04:25:42 | 7             |