l******9
发帖数: 579 | 1 I am sorting two tables on SQL.
The two tables have the same column names and types and rows numbers.
I used order by to do sorting but the two tables are different in order.
Example,
col1 INT
col2 INT
col3 INT
col4 DOUBLE PRECISION
SELECT *
FROM table1 AS t1
ORDER BY t1.col1 , t1.col2, t1.col3, t1.col4 ASC
SELECT *
FROM table2 AS t2
ORDER BY t2.col1 , t2.col2, t2.col3, t2.col4 ASC
Table1 is :
col1 col2 col3 col4
80 790 3498 18654.064361
81 589 3182 2138518.05404
80 518 6742 64613189.0485
81 649 2349 26163.054218
Table2 is :
col1 col2 col3 col4
81 589 3182 2138518.05404
80 518 6742 64613189.0485
80 790 3498 18654.064361
81 649 2349 26163.054218
The sorted results are :
Table1 is :
col1 col2 col3 col4
80 518 6742 64613189.0485
80 790 3498 18654.064361
81 589 3182 2138518.05404
81 649 2349 26163.054218
The sorted results are :
Table12 is :
col1 col2 col3 col4
81 589 3182 2138518.05404
81 649 2349 26163.054218
80 518 6742 64613189.0485
80 790 3498 18654.064361
Why they are different on col1 ?
Thanks | c*****d
发帖数: 6045 | 2 和其他字段无关，多半是col1字段类型不同
试着只对col1排序，并且转换成数值
oracle:
SELECT *
FROM table2 t2
ORDER BY to_number(t2.col1) asc
sql server:
SELECT *
FROM table2 t2
ORDER BY cast(t2.col1 as int) asc | l******9
发帖数: 579 | 3 the types:
col1 INT
col2 INT
col3 INT
col4 DOUBLE PRECISION
thanks !
【在 c*****d 的大作中提到】
: 和其他字段无关，多半是col1字段类型不同
: 试着只对col1排序，并且转换成数值
: oracle:
: SELECT *
: FROM table2 t2
: ORDER BY to_number(t2.col1) asc
: sql server:
: SELECT *
: FROM table2 t2
: ORDER BY cast(t2.col1 as int) asc
| k********e
发帖数: 702 | 4 SELECT *
FROM table2 t2
ORDER BY t2.col1 asc, t2.col2 asc, t2.col3 asc, t2.col4 asc, t2.col5 asc
【在 l******9 的大作中提到】
: the types:
: col1 INT
: col2 INT
: col3 INT
: col4 DOUBLE PRECISION
: thanks !
|
|
