6 Replies Latest reply: May 6, 2012 1:46 AM by Kunwar RSS

    Sorting Problem

    G.Y
      I am sorting data(number) colulmn in descending order, but it is not sorted according to my requirement.
      script
      SELECT colour_code, colour_name
      FROM tbl_Colours
      ORDER BY colour_code desc;

      data inside tbl_colours
      colour_code Colour_name
      1 blue
      . .
      . .
      . .
      98
      99
      .
      100
      .
      .
      241
      ..... mean continue

      Please help
        • 1. Re: Sorting Problem
          indra budiantho
          SELECT colour_code, colour_name
          FROM tbl_Colours
          ORDER BY to_number(colour_code) desc;
          • 2. Re: Sorting Problem
            Most Wanted!!!!
            SELECT   col1, col2
                FROM (SELECT 'a' AS col1, 5 AS col2
                        FROM DUAL
                      UNION
                      SELECT 'b' AS col1, 2 AS col2
                        FROM DUAL
                      UNION
                      SELECT 'c' AS col1, 1 AS col2
                        FROM DUAL
                      UNION
                      SELECT 'd' AS col1, 4 AS col2
                        FROM DUAL
                      UNION
                      SELECT 'e' AS col1, 3 AS col2
                        FROM DUAL) a
            ORDER BY col2 desc
            output:
            COL1     COL2
            
            a     5
            d     4
            e     3
            b     2
            c     1
            and also if u want it like this
            WITH t AS
                 (SELECT '1a' AS col1, 5 AS col2
                    FROM DUAL
                  UNION
                  SELECT '5b' AS col1, 2 AS col2
                    FROM DUAL
                  UNION
                  SELECT '2c' AS col1, 1 AS col2
                    FROM DUAL
                  UNION
                  SELECT '4d' AS col1, 4 AS col2
                    FROM DUAL
                  UNION
                  SELECT '3e' AS col1, 3 AS col2
                    FROM DUAL)
            SELECT  col1, col2
                FROM t
            ORDER BY TO_NUMBER (SUBSTR (col1, 1, 1)) desc
            output :
            COL1     COL2
            
            5b     2
            4d     4
            3e     3
            2c     1
            1a     5
            Edited by: most wanted!!!! on Apr 25, 2012 1:26 AM
            • 3. Re: Sorting Problem
              Himanshu Binjola
              Please do send the table structure, sample data and the column you are sorting on.

              PS: The above post show right way to covert the column to number data type if numeric sorting is required.
              SQL> DESC <TABLE>
              
              {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              • 4. Re: Sorting Problem
                G.Y
                Dear
                What a Wonderful World!
                thanks you so much for help.
                • 5. Re: Sorting Problem
                  BluShadow
                  If that's the correct answer then that indicates that your data is not a NUMBER as you indicated in your original post...

                  >
                  I am sorting data(number) colulmn
                  >

                  How can people correctly help you if you don't provide the correct information in the first place.
                  • 6. Re: Sorting Problem
                    Kunwar
                    i dont know ..but when i check i dont see any issues with your query. as other forum members have pointed out, you can paste the create statement for the table and also the inserts so that you get the correct help.