group by to_number() [message #254521] |
Fri, 27 July 2007 00:55  |
markireland
Messages: 5 Registered: July 2007 Location: Australia
|
Junior Member |
|
|
This code: produces output
select d.my_cd, d.abbr_title, min(a.avail_yr) AS "Min_Year", max(a.avail_yr) AS "Max_Year"
from det_xtr d, spk_xtr a
where d.cat_cd = 'CR'
and a.parent_k_no = d.k_no
and a.parent_ver_no = d.ver_no
and d.my_cd = a.my_cd
group by d.my_cd, d.abbr_title
produces output like this:
my_cd
10010
20200
30333
10010A
20200B
30333C
but what I want is:
my_cd
10010
10010A
20200
20200B
30333
30333C
Is it as simple as
order by to_number(my_cd) ?
|
|
|
|
|
|
|
|
Re: group by to_number() [message #254560 is a reply to message #254551] |
Fri, 27 July 2007 02:27   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Are you looking for something like this?
SQL> --
SQL> -- On 10g
SQL> --
SQL> WITH yourtable AS
2 (
3 SELECT '10010' col1 FROM dual UNION ALL
4 SELECT '20200' col1 FROM dual UNION ALL
5 SELECT '30333' col1 FROM dual UNION ALL
6 SELECT '10010A' col1 FROM dual UNION ALL
7 SELECT '20200B' col1 FROM dual UNION ALL
8 SELECT '30333C' col1 FROM dual
9 )
10 SELECT col1
11 FROM yourtable
12 ORDER BY TO_NUMBER(regexp_replace(col1,'[^[:digit:]]')), col1
13 /
COL1
------
10010
10010A
20200
20200B
30333
30333C
6 rows selected.
SQL>
SQL> --
SQL> -- Without regular expressions:
SQL> --
SQL> WITH yourtable AS
2 (
3 SELECT '10010' col1 FROM dual UNION ALL
4 SELECT '20200' col1 FROM dual UNION ALL
5 SELECT '30333' col1 FROM dual UNION ALL
6 SELECT '10010A' col1 FROM dual UNION ALL
7 SELECT '20200B' col1 FROM dual UNION ALL
8 SELECT '30333C' col1 FROM dual
9 )
10 SELECT col1
11 FROM yourtable
12 ORDER BY TO_NUMBER( REPLACE
13 ( TRANSLATE
14 ( col1
15 , REPLACE
16 ( TRANSLATE
17 ( col1
18 , '0123456789'
19 , '$'
20 )
21 , '$'
22 )||'$'
23 , '$'
24 )
25 , '$'
26 )
27 )
28 , col1
29 /
COL1
------
10010
10010A
20200
20200B
30333
30333C
6 rows selected.
Warning: I am not yet fully awake, no guarantees .
MHE
|
|
|
Re: group by to_number() [message #254871 is a reply to message #254521] |
Sun, 29 July 2007 18:54   |
markireland
Messages: 5 Registered: July 2007 Location: Australia
|
Junior Member |
|
|
Here are the cases in english:
Most codes are numbers.
Like
234
00125
123
12345
There are many codes with a capital letter on the end.
Like
123Z
12333J
I realize now that I can have
GROUP BY my_cd, my_description
ORDER BY to_char(my_cd)
It is still a slow query. Is there a quicker method?
|
|
|
Re: group by to_number() [message #254910 is a reply to message #254871] |
Mon, 30 July 2007 01:14  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Post a new topic in Performances forum.
Read the sticky before posting in order to provide all useful informations.
Regards
Michel
|
|
|