Home » SQL & PL/SQL » SQL & PL/SQL » sort problem with decimal (10g, win 8.1)
| sort problem with decimal [message #650068] |
Wed, 13 April 2016 22:49  |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Below is my table with two fields.
create table t1(
sizeID number(5) Primary Key,
sizeName varchar2(10)
);
insert into t1 values(1,'3x6');
insert into t1 values(2,'4x6');
insert into t1 values(3,'6x6');
insert into t1 values(4,'6x7');
insert into t1 values(5,'6x8');
insert into t1 values(6,'6x9');
insert into t1 values(7,'6x10');
insert into t1 values(8,'6x11');
insert into t1 values(9,'6x12');
insert into t1 values(10,'9x6');
insert into t1 values(11,'9x7');
insert into t1 values(12,'9x8');
insert into t1 values(13,'9x9');
insert into t1 values(14,'9x10.5');
insert into t1 values(15,'9x10.7');
insert into t1 values(16,'9x12');
insert into t1 values(17,'9x10.2');
insert into t1 values(18,'9x15');
insert into t1 values(19,'9x16');
insert into t1 values(20,'9x18');
insert into t1 values(21,'12x8');
insert into t1 values(22,'12x9');
insert into t1 values(23,'12x10');
insert into t1 values(24,'12x12');
insert into t1 values(25,'12x14');
insert into t1 values(26,'12x15');
insert into t1 values(27,'12x16');
insert into t1 values(28,'12x18');
insert into t1 values(29,'15x14');
insert into t1 values(30,'15x15');
insert into t1 values(31,'15x16');
insert into t1 values(32,'15x18');
insert into t1 values(33,'12x22');
insert into t1 values(34,'2x3.5');
insert into t1 values(35,'4x300 ROLE');
insert into t1 values(36,'8x9');
insert into t1 values(37,'8x10');
insert into t1 values(38,'8x12');
insert into t1 values(39,'4x10');
insert into t1 values(40,'4x15');
insert into t1 values(41,'4x20');
insert into t1 values(42,'4x25');
insert into t1 values(43,'4x30');
insert into t1 values(44,'4x35');
insert into t1 values(45,'4x40');
insert into t1 values(46,'4x45');
insert into t1 values(47,'4x50');
insert into t1 values(48,'5x7');
insert into t1 values(49,'7x7.5');
insert into t1 values(50,'2.5x4 LJ');
I want the result to be sorted completely including decimal point after 'x.
my query is
select sizeid,sizename from t1
order by regexp_substr(sizename, '^\D*') nulls first,
to_number(regexp_substr(sizename, '\d+'))
/
SIZEID SIZENAME
---------- ----------
34 2x3.5
50 2.5x4 LJ
1 3x6
2 4x6
35 4x300 ROLE
39 4x10
40 4x15
41 4x20
42 4x25
43 4x30
44 4x35
47 4x50
46 4x45
45 4x40
48 5x7
3 6x6
9 6x12
8 6x11
7 6x10
6 6x9
5 6x8
4 6x7
49 7x7.5
38 8x12
37 8x10
36 8x9
10 9x6
11 9x7
12 9x8
13 9x9
14 9x10.5
15 9x10.7
16 9x12
17 9x10.2
18 9x15
19 9x16
20 9x18
24 12x12
23 12x10
25 12x14
26 12x15
21 12x8
22 12x9
27 12x16
33 12x22
28 12x18
30 15x15
29 15x14
31 15x16
32 15x18
50 rows selected.
at sizeid 14,15,16,17 the result is
14 9x10.5
15 9x10.7
16 9x12
17 9x10.2
My Question is
the result should be sorted like below (the number(including digits after decimal) after 'x'
ie
17 9x10.2
14 9x10.5
15 9x10.7
16 9x12
Can any one guide me please?
[Updated on: Wed, 13 April 2016 23:32] Report message to a moderator |
|
|
|
| Re: sort problem with decimal [message #650069 is a reply to message #650068] |
Wed, 13 April 2016 23:52  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following shows you what values your regular expressions return, that you are using for ordering. So, these four have the same order and that is just the random order they came out in.
SCOTT@orcl> select sizeid, sizename,
2 regexp_substr (sizename, '^\D*') order1,
3 to_number (regexp_substr (sizename, '\d+')) order2
4 from t1
5 where sizeid in (14, 15, 16, 17)
6 order by regexp_substr (sizename, '^\D*') nulls first,
7 to_number (regexp_substr (sizename, '\d+'))
8 /
SIZEID SIZENAME ORDER1 ORDER2
---------- ---------- ---------------------------------------- ----------
14 9x10.5 9
17 9x10.2 9
16 9x12 9
15 9x10.7 9
4 rows selected.
|
|
|
|
| Re: sort problem with decimal [message #650071 is a reply to message #650069] |
Thu, 14 April 2016 00:10 |
|
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You could try something like this:
SCOTT@orcl> select sizeid, sizename,
2 to_number (regexp_substr (sizename, '\d+', 1, 1)) order1,
3 to_number (regexp_substr (sizename, '\d+', 1, 2)) order2,
4 to_number (regexp_substr (sizename, '\d+', 1, 3)) order3
5 from t1
6 where sizeid in (14, 15, 16, 17)
7 order by to_number (regexp_substr (sizename, '\d+', 1, 1)),
8 to_number (regexp_substr (sizename, '\d+', 1, 2)),
9 to_number (regexp_substr (sizename, '\d+', 1, 3))
10 /
SIZEID SIZENAME ORDER1 ORDER2 ORDER3
---------- ---------- ---------- ---------- ----------
17 9x10.2 9 10 2
14 9x10.5 9 10 5
15 9x10.7 9 10 7
16 9x12 9 12
4 rows selected.
|
|
|
|
| Re: sort problem with decimal [message #650073 is a reply to message #650071] |
Thu, 14 April 2016 01:18 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Salute to you Barbara Boehmer
it worked fine...
1 select sizeid, sizename
2 from t1
3 order by to_number (regexp_substr (sizename, '\d+', 1, 1)),
4 to_number (regexp_substr (sizename, '\d+', 1, 2)),
5* to_number (regexp_substr (sizename, '\d+', 1, 3))
SAM@orcl:>/
SIZEID SIZENAME
---------- ----------
34 2x3.5
50 2.5x4 LJ
1 3x6
2 4x6
39 4x10
40 4x15
41 4x20
42 4x25
43 4x30
44 4x35
45 4x40
46 4x45
47 4x50
35 4x300 ROLE
48 5x7
3 6x6
4 6x7
5 6x8
6 6x9
7 6x10
8 6x11
9 6x12
49 7x7.5
36 8x9
37 8x10
38 8x12
10 9x6
11 9x7
12 9x8
13 9x9
17 9x10.2
14 9x10.5
15 9x10.7
16 9x12
18 9x15
19 9x16
20 9x18
21 12x8
22 12x9
23 12x10
24 12x12
25 12x14
26 12x15
27 12x16
28 12x18
33 12x22
29 15x14
30 15x15
31 15x16
32 15x18
50 rows selected.
rzkhan
[Updated on: Thu, 14 April 2016 01:20] Report message to a moderator |
|
|
|
| Re: sort problem with decimal [message #650080 is a reply to message #650073] |
Thu, 14 April 2016 08:52 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I am not so sure Barbara's solution is a working one. I assume, based on column name sizename, 9x10.2 is 9 by 10.2. And Barbara's solution doesn't deal with fractions properly:
SQL> create table t1(
2 sizeID number(5) Primary Key,
3 sizeName varchar2(10)
4 );
Table created.
SQL> insert into t1 values(14,'9x10.5');
1 row created.
SQL> insert into t1 values(15,'9x10.07');
1 row created.
SQL> insert into t1 values(16,'9x10.12');
1 row created.
SQL> select sizeid, sizename
2 from t1
3 order by to_number (regexp_substr (sizename, '\d+', 1, 1)),
4 to_number (regexp_substr (sizename, '\d+', 1, 2)),
5 to_number (regexp_substr (sizename, '\d+', 1, 3))
6 /
SIZEID SIZENAME
---------- ----------
14 9x10.5
15 9x10.07
16 9x10.12
As you can see sizes are not ordered properly.
SY.
|
|
|
|
| Re: sort problem with decimal [message #650081 is a reply to message #650080] |
Thu, 14 April 2016 09:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Also, Barbara's solution doesn't account for both fractional sizes, e.g. 2.5x4.5. You can adjust it:
select sizeid,
sizename
from t1
order by to_number(regexp_substr(sizename,'(\d|\.)+',1,1)),
to_number(regexp_substr(sizename,'(\d|\.)+',1,2))
/
SIZEID SIZENAME
---------- ----------
15 9x10.07
16 9x10.12
14 9x10.5
SQL>
SY.
|
|
|
|
| Re: sort problem with decimal [message #650092 is a reply to message #650068] |
Fri, 15 April 2016 00:25 |
rzkhan
Messages: 370
Registered: March 2005
|
Senior Member |
|
|
Thanks a lot for pointing out the correction...As for my data it only containts decimal after the symbol 'x' and does not contain 0 at all.
However, I am grateful...
rzkhan
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 19:23:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
