Thread: Sorting Days of the Week ?
06-05-06, 06:05 #1Registered User
- Join Date
- Jun 2005
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
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 ?Learning, Keep on learning.....
06-06-06, 10:20 #2Registered User
- Join Date
- Sep 2004
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
By the same reasoning,Code:
ORDER BY locate('A','A')
Last edited by Peter.Vanroose; 06-06-06 at 10:43.--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting