# Thread: Sorting Days of the Week ?

1. Registered User
Join Date
Jun 2005
Location
Chennai
Posts
16

## Unanswered: Sorting Days of the Week ?

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 ?

2. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
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:
Code:
`ORDER BY case when 1=1 then 2 else 2 end`
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,
Code:
`ORDER BY locate('A','A')`
is not the same as "ORDER BY 1": it will not sort the rows at all.
Last edited by Peter.Vanroose; 06-06-06 at 10:43.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•