Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: Ordering a column in descending with numbers first

    Hi Guys,

    I have a Column which has both numeric values and Alphabets in them.

    I want to sort them in descending with numbers first and then Alphabets later.

    Example:

    Source column:
    2008
    2005
    2010
    2013
    All-Year
    Month-year

    Required Output:

    Source Column:
    2013
    2010
    2008
    2005
    Month-Year
    All-year

    Thanks for your help in advance.

    Cheers!!!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @t table (
       source_column varchar(20)
    );
    
    INSERT INTO @t (source_column)
      VALUES ('2008')
           , ('2005')
           , ('2010')
           , ('2013')
           , ('All-Year')
           , ('Month-year');
    
    SELECT source_column
    FROM   (
            SELECT source_column
                 , Cast(textual_numbers As int) As actual_numbers
            FROM   (
                    SELECT source_column
                         , CASE WHEN IsNumeric(source_column) = 1 THEN source_column END As textual_numbers
                    FROM   @t
                   ) As a_subquery
           ) As another_subquery
    ORDER
        BY actual_numbers DESC
         , source_column DESC
    ;
    
    -- Shorter version
    --  Only use if you fully understand the above query as you or someone else will no doubt have to maintain this in the future
    SELECT source_column
    FROM   @t
    ORDER
        BY Cast(CASE WHEN IsNumeric(source_column) = 1 THEN source_column END As int) DESC
         , source_column DESC
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    Thanks George,,

    I understand the short version and going with it as of now..
    One question, though, instead of using the entire CAST expression , why cant we just use "IsNumeric(SourceColumn) Desc" alone..

    Can you throw some light on this..

    Thanks once again for your help.. Cheers!!!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes, you could do that. Try it for yourself and see
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This is not a valid column. A column is a scalar value drawn from one and only one domain (i.e. hat size and pant size do not go together). You have put years (temporal durations) and vague meta data in this nameless column! You need to scrub the data before it gets to the schema.

    I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
    Your first values on the list would become:

    '2008-00-00'
    '2005-00-00'
    '2010-00-00'
    '2013-00-00'

    But non-values like 'All-Year' and 'Month-year' need to be qualified as to which particular year and month is involved. Ever see the tee-shirt slogan “On a scale from 1 to 10, what color is your favorite letter of the alphabet?” Ever hear of the Law of Identity? (“to be is to be something in particular; to be nothing in particular or anything in general is to be nothing at all”)

Posting Permissions

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