MVSFORUMS.com

sramesh · Posted: Sun Jun 04, 2006 11:35 pm Post subject: 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

Can anyone explain how the above sql statement sorts the day_name in correct order ?
_________________
with regards,
S.Ramesh
"A problem well stated is a problem half solved"

Bill Dennis · Posted: Mon Jun 05, 2006 11:20 am Post subject:

The most logical explanation for this would be that the day is replaced by the number representing it's place in the list for sorting purposes, SUN=001,MON=004, etc. The sort can order them based on your desired sequence.

I am new to DB2 also, but this is my best guess.
_________________
Regards,
Bill Dennis

Disclaimer: My comments on this foorum are my own and do not represent the opinions or suggestions of any other person or business entity.