Home » SQL & PL/SQL » SQL & PL/SQL » ORDER BY Question
ORDER BY Question [message #387721] Fri, 20 February 2009 07:05 Go to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
Greetings All!

We are trying to develop a Java based application which queries a VIEW. This application is suppose to have the same functionality of an old COBOL based application.
The VIEW is created by a simple query joining two tables using ORDER BY.

Here's a sample query output -
COL1 COL2 COL3  COL4
---------------------
A    AA   DATA1 DATA2
A    AB   DATA1 DATA2
A    AC   DATA1 DATA2
A    A1   DATA1 DATA2
A    A2   DATA1 DATA2
A    A3   DATA1 DATA2
B    AA   DATA1 DATA2
B    AB   DATA1 DATA2
B    AC   DATA1 DATA2
B    A1   DATA1 DATA2
B    A2   DATA1 DATA2
B    A3   DATA1 DATA2

Here's what need to get in COL2 instead -
COL1 COL2 COL3  COL4
---------------------
A    A1   DATA1 DATA2
A    A2   DATA1 DATA2
A    A3   DATA1 DATA2
A    AA   DATA1 DATA2
A    AB   DATA1 DATA2
A    AC   DATA1 DATA2
B    A1   DATA1 DATA2
B    A2   DATA1 DATA2
B    A3   DATA1 DATA2
B    AA   DATA1 DATA2
B    AB   DATA1 DATA2
B    AC   DATA1 DATA2

We want to have the ascending order of number instead of alphabets in the second position of COL2.

Is this in any way possible to do in SQL?

Thanking you all.

[EDITED by LF: applied [pre] tags to preserve formatting]

[Updated on: Fri, 20 February 2009 07:09] by Moderator

Report message to a moderator

Re: ORDER BY Question [message #387726 is a reply to message #387721] Fri, 20 February 2009 07:13 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you correctly, you don't have to do anything - numbers will be displayed before letters:
SQL> with test as
  2    (select 'AA' col from dual union
  3     select 'AB' from dual union
  4     select 'A1' from dual union
  5     select 'AC' from dual union
  6     select 'A3' from dual
  7    )
  8  select col from test
  9  order by col;

CO
--
A1
A3
AA
AB
AC

SQL>

Though, if your real life situation isn't that simple, you could always order by SUBSTR(col, 2, 1).
Re: ORDER BY Question [message #387727 is a reply to message #387721] Fri, 20 February 2009 07:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If you would use a normal order by then you would get what you want with numbers before characters.

SQL> SELECT * FROM (
  2  SELECT 'AA' FROM dual
  3  UNION
  4  SELECT 'A1' FROM dual
  5  ) ORDER BY 1;

'A
--
A1
AA


So how does your order by actually look?
Re: ORDER BY Question [message #387728 is a reply to message #387727] Fri, 20 February 2009 07:15 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ho-ho-ho!!! ./fa/1940/0/
Re: ORDER BY Question [message #387735 is a reply to message #387727] Fri, 20 February 2009 07:40 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
I will try out all the suggestions. I like to point out that in COL2 values I still want to have the first position to be alphabets in ascending order while the second position to be based of ascending order by numbers not alphabets.
Do I need to use the substr function individually for both the first and second postion of COL2 with order by to achieve what I want?

By the way, WOW! you guys reply pretty fast.

Thank you all.
Re: ORDER BY Question [message #387737 is a reply to message #387735] Fri, 20 February 2009 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
tariqahsan wrote on Fri, 20 February 2009 13:40
I like to point out that in COL2 values I still want to have the first position to be alphabets in ascending order while the second position to be based of ascending order by numbers not alphabets.



Does that mean you have records where the first character of col2 is numeric?
Re: ORDER BY Question [message #387739 is a reply to message #387735] Fri, 20 February 2009 07:48 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
I tried -

with test as
(select 'AA' col from dual union
select 'AB' from dual union
select 'A1' from dual union
select 'AC' from dual union
select 'A3' from dual
)
select col from test
order by col
/

CO
--
AA
AB
AC
A1
A3

Not getting the intended -

CO
--
A1
A3
AA
AB
AC
Re: ORDER BY Question [message #387741 is a reply to message #387721] Fri, 20 February 2009 07:52 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
In respect to my earlier mail wondering if there any Oracle environment setup I need to check and setup to get the order the way I expect to happen.
Re: ORDER BY Question [message #387745 is a reply to message #387739] Fri, 20 February 2009 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: ORDER BY Question [message #387747 is a reply to message #387721] Fri, 20 February 2009 08:08 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm guessing it's an nls setting of some description.

What does this give:

SELECT * FROM NLS_DATABASE_PARAMETERS;

[Updated on: Fri, 20 February 2009 08:09]

Report message to a moderator

Re: ORDER BY Question [message #387758 is a reply to message #387721] Fri, 20 February 2009 08:41 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER VALUE
------------------------------ ---------------------
NLS_SORT BINARY

Is this correct value? Do I have to check some other parameter column value in that value?
Re: ORDER BY Question [message #387762 is a reply to message #387721] Fri, 20 February 2009 08:45 Go to previous messageGo to next message
tariqahsan
Messages: 13
Registered: February 2009
Junior Member
By the way heres the full query output -

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8EBCDIC1047
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_SAVED_NCHAR_CS WE8EBCDIC1047
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 10.1.0.5.21

21 rows selected.

SQL>
Re: ORDER BY Question [message #387821 is a reply to message #387762] Fri, 20 February 2009 14:03 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Would something like this help?
SQL> with test as
  2  (select 'AA' col from dual union
  3  select 'AB' from dual union
  4  select 'A1' from dual union
  5  select 'AC' from dual union
  6  select 'A3' from dual
  7  )
  8  select col from test
  9  order by col;

CO
--
AA
AB
AC
A1
A3

SQL> with test as
  2  (select 'AA' col from dual union
  3  select 'AB' from dual union
  4  select 'A1' from dual union
  5  select 'AC' from dual union
  6  select 'A3' from dual
  7  )
  8  select col from test
  9  order by nlssort(col, 'nls_sort = binary');

CO
--
A1
A3
AA
AB
AC

SQL>

[Updated on: Fri, 20 February 2009 14:03]

Report message to a moderator

Re: ORDER BY Question [message #387834 is a reply to message #387821] Fri, 20 February 2009 15:10 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
nls_sort is already set to binary.

I am just having a hard time believing that it is not working for the OP.

to OP:

I want to see an output of a true SQL session, not a cut and paste of command you think you ran and possibly forged output. I do not see any SQL prompts or the typical Oracle feedback.

What is your client?
What full version of Oracle?
Previous Topic: count of calls from a procedure or package
Next Topic: dblink variable refuses to compile
Goto Forum:
  


Current Time: Mon Dec 05 12:53:52 CST 2016

Total time taken to generate the page: 0.20217 seconds