Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2005
    Location
    Chennai
    Posts
    16

    Question 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 ?
    Learning, Keep on learning.....

  2. #2
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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