The sql_clause parameter not intended to update the sorted fields. It's in part of filter the features in order to iterate within Cursor either Update (or) Search.
For sorted field, we have to use Sort_management to sort the STREET field in to temporary feature class and written back in to original feature class (Or)
use this code to overwrite with the sorted rows
lyr = 'C:\Temp\Default.gdb\CurrntRoadNamesTablePublic_1'
datatosort = []
with arcpy.da.SearchCursor(lyr,"*") as cursor:
for row in cursor:
datatosort.append(row)
#STREET field Index
streetindex = 2
rowindex = 0
datatosort.sort(key=lambda tup: tup[streetindex])
with arcpy.da.UpdateCursor(lyr,"*") as cursor:
for row in cursor:
row = datatosort[rowindex]
rowindex += 1
cursor.updateRow(row)
Change the streetindex value as per STREET field index