Re-ordering fields permanently using ArcGIS Make Query Table tool?

I figured out how to do this by using the Make Query Table tool, Copy As Python Snippet, the Python window and the Copy Features tool.

After running the Make Query Table tool to pull through just the fields I wanted to appear in the output I was able to Copy As Python Snippet this code from the Geoprocessing | Results window into the Python window of ArcMap.

arcpy.MakeQueryTable_management("'C:/avhome/data/WAregional/wa regional.mdb/schools'","QueryTable","USE_KEY_FIELDS","#","schools.OBJECTID #;schools.Shape #;schools.CODE #;schools.NAME #;schools.TYPE #;schools.Y11STUDENT #;schools.Y12STUDENT #;schools.COORDGEOCO #;schools.ID #","#")

and edit it to become:

arcpy.MakeQueryTable_management("'C:/avhome/data/WAregional/wa regional.mdb/schools'","QueryTable2","USE_KEY_FIELDS","#","schools.OBJECTID #;schools.Shape #;schools.Y12STUDENT #;schools.Y11STUDENT #;schools.NAME #","#")

Note that the new QueryTable2 retains the Shape field (so I can CopyFeatures it) and I have reordered the NAME,YR11STUDENT & YR12STUDENT fields. I also used the opportunity to drop out a few more fields.

The last step is to use the Copy Features tool on QueryTable2 which I did via its tool dialog to create a new feature class with the fields re-ordered permanently.

With the Merge Tool, you can easily reorder fields permanently. It works with tables and feature classes. The reordering can be done through python script and even with the Tool dialog (By removing a field and re-adding it in the dialog). Although re-ordering via the dialog is not a perfect approach.

It is recommended to use Merge tool once and then use Copy As Python Snippet and then manually change the fields orders and then paste the python code in python windows.

Here is a python script that uses the Merge Tool to reorder fields (Copied from here)

import arcpy

def reorder_fields(table, out_table, field_order, add_missing=True):
    """ 
    Reorders fields in input featureclass/table
    :table:         input table (fc, table, layer, etc)
    :out_table:     output table (fc, table, layer, etc)
    :field_order:   order of fields (objectid, shape not necessary)
    :add_missing:   add missing fields to end if True (leave out if False)
    -> path to output table
    """
    existing_fields = arcpy.ListFields(table)
    existing_field_names = [field.name for field in existing_fields]

    existing_mapping = arcpy.FieldMappings()
    existing_mapping.addTable(table)

    new_mapping = arcpy.FieldMappings()

    def add_mapping(field_name):
        mapping_index = existing_mapping.findFieldMapIndex(field_name)

        # required fields (OBJECTID, etc) will not be in existing mappings
        # they are added automatically
        if mapping_index != -1:
            field_map = existing_mapping.fieldMappings[mapping_index]
            new_mapping.addFieldMap(field_map)

    # add user fields from field_order
    for field_name in field_order:
        if field_name not in existing_field_names:
            raise Exception("Field: {0} not in {1}".format(field_name, table))

        add_mapping(field_name)

    # add missing fields at end
    if add_missing:
        missing_fields = [f for f in existing_field_names if f not in field_order]
        for field_name in missing_fields:
            add_mapping(field_name)

    # use merge with single input just to use new field_mappings
    arcpy.Merge_management(table, out_table, new_mapping)
    return out_table

USAGE:

new_field_order = ["field2", "field3", "field1"]
reorder_fields(in_fc, out_fc, new_field_order)