The result of a query is a table with a set of rows.
The order is not guaranteed to be the same for each query.
To insist on getting the resulting rows of your query back in a guaranteed order, use the ORDER BY clause in your SELECT command statements.
You can specify multiple sort specifications, separated by commas.
Each sort specification consists of a column specification or column expression, optionally followed by the keyword DESC (descending), in case you want to sort in descending order.
Without this addition, the default sorting order is ASC (ascending).
You can use ASC to denote that it is ascending order.
The column specification may consist of a single column name or a column expression.
To refer to columns in the ORDER BY clause, you can use any of the following:
Column ordinal numbers in the ORDER BY clause have no relationship with the order of the columns in the database.
They are dependent on only the SELECT clause of your query.
Using column aliases instead increases SQL statement readability, and ensures your ORDER BY clauses become independent of the SELECT clauses of your queries.
The following code shows how you can sort query results on column combinations.
As you can see, the query result is sorted on department number, and then on employee name for each department.
select deptno, ename, init, msal from emp where msal < 1500 order by deptno, ename;
You can reverse the default sorting order by adding the DESC keyword to your ORDER BY clause.
select ename, 12*msal+comm as yearsal from emp where job = 'SALESREP' order by yearsal desc;
SQL> SQL> drop table emp; Table dropped.-- from w w w. j ava 2s . co m SQL> create table emp( 2 empno NUMBER(4) primary key, 3 ename VARCHAR2(8) not null , 4 init VARCHAR2(5) not null , 5 job VARCHAR2(8) , 6 mgr NUMBER(4) , 7 bdate DATE not null , 8 msal NUMBER(6,2) not null , 9 comm NUMBER(6,2) , 10 deptno NUMBER(2) default 10) ; SQL> SQL> insert into emp values(7001,'SMITH','N', 'TRAINER', 7902,date '1975-12-17', 1800 , NULL, 20); SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20', 1600, 300, 30); SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02', 1250, 500, 10); SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02', 2975, NULL, 20); SQL> insert into emp values(7005,'BROWN','P', 'SALESREP',7006,date '1976-09-28', 1250, 1400, 30); SQL> insert into emp values(7006,'BLAKE','R', 'MANAGER', 7009,date '1973-11-01', 2850, NULL, 10); SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09', 2450, NULL, 10); SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26', 3000, NULL, 20); SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17', 5000, NULL, 10); SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28', 1500, 0, 30); SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30', 1100, NULL, 20); SQL> insert into emp values(7012,'JONES','R', 'ADMIN', 7006,date '1979-10-03', 8000, NULL, 30); SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13', 3000, NULL, 20); SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN', 7007,date '1972-01-23', 1300, NULL, 10); SQL> SQL> SQL> select deptno, ename, init, msal 2 from emp 3 where msal < 1500 4 order by deptno, ename; DEPTNO | ENAME | INIT | MSAL --------- | -------- | ----- | --------- 1 | MARY | ABC | 01300.00 1 | WARD | TF | 01250.00 2 | ADAMS | AA | 01100.00 30 | BROWN | P | 01250.00 SQL> SQL> select ename, 12*msal+comm as yearsal 2 from emp 3 where job = 'SALESREP' 4 order by yearsal desc; ENAME | YEARSAL -------- | --------- ALLEN | 19500.00 BREAD | 18000.00 BROWN | 16400.00 WARD | 15500.00 SQL>