order by oracle and sql server

[hensa22]

hi everyone

I have a queation which i have not explain.

this query

SELECT N.* FROM (
(SELECT '5' M FROM DUAL
UNION
SELECT 'AS' M FROM DUAL) ) N
ORDER BY N.M AS

on oracle return a recordset as:
-----------------
AS
5
-------------------


but this query on sql server

SELECT * FROM (
SELECT '5' AS M
UNION
SELECT 'AS' AS M) AS N
ORDER BY N.M ASC
returns
-------------------------------
5
AS
------------------------------


any explain ???

thanks in advance

[olivthill2]

Yes, Oracle's behaviour is strange. Thanks for sharing it here.

It seems the problem is caused by the presence of both numbers and alphabetical characters, because the queries below are giving the expected results:
SELECT N.* FROM (
(SELECT 'A' M FROM DUAL
UNION
SELECT 'B' M FROM DUAL) ) N
ORDER BY N.M ASC

SELECT N.* FROM (
(SELECT 'B' M FROM DUAL
UNION
SELECT 'A' M FROM DUAL) ) N
ORDER BY N.M ASC

SELECT N.* FROM (
(SELECT '5' M FROM DUAL
UNION
SELECT '6' M FROM DUAL) ) N
ORDER BY N.M ASC

SELECT N.* FROM (
(SELECT '6' M FROM DUAL
UNION
SELECT '5' M FROM DUAL) ) N
ORDER BY N.M ASC

[davide++]

Hi all.

I've tried in Oracle 8.1.7.4 instance, and the result was what I expected


SELECT N.* FROM (
SELECT 'AS' M FROM dual
UNION
SELECT '5' M FROM dual) N
ORDER BY N.M ASC

M
--
5
AS



But I noticed that Oracle shows some little (!!!) problem in sorting operations; for example, if you put column number instead of column name in ORDER BY clause, sometime sorting fails (except with the first column)

[hensa22]

hi davide thanks for reply well, my tests are on 10g,seem on oracle 8 result it's the expected. I am looking for any solution, any helps, thanks.