Home » SQL & PL/SQL » SQL & PL/SQL » group by to_number()
group by to_number() [message #254521] Fri, 27 July 2007 00:55 Go to next message
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 #254526 is a reply to message #254521] Fri, 27 July 2007 01:06 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, but no. You can not TO_NUMBER('10010A') as you'll get an invalid number error. Remove character(s), then use TO_NUMBER function.

BTW, GROUP BY clause does not order records - ORDER BY does.
Re: group by to_number() [message #254531 is a reply to message #254526] Fri, 27 July 2007 01:15 Go to previous messageGo to next message
markireland
Messages: 5
Registered: July 2007
Location: Australia
Junior Member
But how do I use the to_number() in my group by clause?

In other words, how can I achieve the sort order that I want?

Thanks
Re: group by to_number() [message #254538 is a reply to message #254531] Fri, 27 July 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ GROUP BY does not order, ONLY ORDER BY does
2/ Do you show ALL the cases we can have that is: a number or a number followed by 1 character?
3/
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).


Regards
Michel
Re: group by to_number() [message #254547 is a reply to message #254521] Fri, 27 July 2007 01:48 Go to previous messageGo to next message
markireland
Messages: 5
Registered: July 2007
Location: Australia
Junior Member
OK So I must use order by.

these are all the cases
122
123A
12345
12345A
Re: group by to_number() [message #254551 is a reply to message #254547] Fri, 27 July 2007 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Say in WORDS what are all possible cases.

In your last example you only show A as character in the first there were also B and C.

Regards
Michel
Re: group by to_number() [message #254560 is a reply to message #254551] Fri, 27 July 2007 02:27 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Very Happy.
MHE
Re: group by to_number() [message #254871 is a reply to message #254521] Sun, 29 July 2007 18:54 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Stale Updates and Explicit Cursors
Next Topic: "Having clause" restrict row & groups or groups only ?
Goto Forum:
  


Current Time: Mon Dec 05 21:01:14 CST 2016

Total time taken to generate the page: 0.23784 seconds