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

# Sorting Problem

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

• ###### 1. Re: Sorting Problem
``````SELECT colour_code, colour_name
FROM tbl_Colours
ORDER BY to_number(colour_code) desc;``````
• ###### 2. Re: Sorting Problem
``````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
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
Dear
What a Wonderful World!
thanks you so much for help.
• ###### 5. Re: Sorting Problem
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
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.