Standard SQL and JOIN ( SELECT ... )

In MySQL I use a lot constructs which look like this:

LEFT JOIN(
    SELECT field_one, field_two, field_three FROM (
        SELECT field_one, field_two, field_three FROM table_one
        ORDER BY field_three 
    ) o GROUP BY field_one
) abc ON cur_table.some_field = abc.field_one

I use it, because it helps me to implement a usual task - to order some data before grouping. Now I wonder if it is possible to migrate this sql construct from MySQL to SQLite and Postgresql.