You should read the documentation.
DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables).
TOP is only supported bySQL Serverdatabases.
For a shapefile, you will need to read all the data into a list, and then enumerate the sorted list. Here's an untested code sample.
import arcpy
subs_shp = 'C:\Temp\SUBS.shp'
sub_name_values = [row[0] for row in arcpy.da.SearchCursor(subs_shp,["SUB_NAME"])]
sub_name_id = {}
for enum, sub_name in enumerate(sorted(sub_name_values)):
sub_name_id[sub_name] = enum
with arcpy.da.UpdateCursor(subs_shp,["ID","SUB_NAME"]) as u_cursor:
for id, sub_name in u_cursor:
id = sub_name_id[sub_name]
u_cursor.updateRow([id, sub_name])