e.g Atable having a column day_name char(3) for days of the week.
SELECT DAY_NAME FROM ACCT_TABLE ORDER BY DAY_NAME;
Result will be..
FRI, MON, SAT, SUN, THU, TUE, WED
But this is not correct right ?...
Answer : Select day_name from ACCT_TABLE order by locate(day_name,'SUNMONTUEWEDTHUFRISAT');

Now the result is
SUN
MON
...
...
SAT

Description: To understand how the LOCATE function works:

It returns the starting position of the first occurrence of one string within another string. So, in our example, LOCATE finds the position of the DAY_NAME value within the string 'SUNMONTUEWEDTHUFRISAT', and returns the integer value of that position. If DAY_NAME is WED, the LOCATION function in the above SQL statement returns 10.

My doubt: "order by 2" means it takes the second column for sort
Actually 2 refers column number

Can anyone explain how the above sql statement sorts the day_name in correct order ?

The expression "ORDER BY 2" must have a literal "2" in order for this to sort by the second column.
In all other cases, the expression (like in your example "locate") is applied to all rows individually. Even if it's aconstant.

That's the power of a 4th generation language!

So for example the following will not sort the rows at all:since "2" is now a constant expression, applied to all rows individually, resulting in all rows having the same sort key.

By the same reasoning,is not the same as "ORDER BY 1": it will not sort the rows at all.