Order by a calculated column : Order By « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. Order By
SQL>
SQL> CREATE TABLE titles(
  2    title_id   CHAR(3)      NOT NULL,
  3    title_name VARCHAR(40)  NOT NULL,
  4    type       VARCHAR(10)  NULL    ,
  5    pub_id     CHAR(3)      NOT NULL,
  6    pages      INTEGER      NULL    ,
  7    price      DECIMAL(5,2) NULL    ,
  8    sales      INTEGER      NULL    ,
  9    pubdate    DATE         NULL    ,
 10    contract   SMALLINT     NOT NULL
 11  );

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO titles VALUES('T01','Java','history','P01',111,21.99,566,DATE '2000-08-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T02','Oracle','history','P03', 114,19.95,9566,DATE '1998-04-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T03','SQL','computer','P02', 122,39.95,25667,DATE '2000-09-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T04','C++','psychology','P04', 511,12.99,13001,DATE '1999-05-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T05','Python','psychology','P04', 101,6.95,201440,DATE '2001-01-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T06','JavaScript','biography','P01', 173,19.95,11320,DATE '2000-07-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T07','LINQ','biography','P03', 331,23.95,1500200,DATE '1999-10-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T08','C#','children','P04', 861,10.00,4095,DATE '2001-06-01',1);

1 row created.

SQL> INSERT INTO titles VALUES('T09','SQL Server','children','P04', 212,13.95,5000,DATE '2002-05-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T10','AJAX','biography','P01', NULL,NULL,NULL,NULL,0);

1 row created.

SQL> INSERT INTO titles VALUES('T11','VB','psychology','P04', 821,7.99,94123,DATE '2000-11-30',1);

1 row created.

SQL> INSERT INTO titles VALUES('T12','Office','biography','P01', 507,12.99,100001,DATE '2000-08-31',1);

1 row created.

SQL> INSERT INTO titles VALUES('T13','VBA','history','P03', 812,29.99,10467,DATE '1999-05-31',1);

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL> SELECT title_id,
  2         price,
  3         sales,
  4         price * sales AS "Revenue"
  5    FROM titles
  6    ORDER BY "Revenue" DESC;

TIT      PRICE      SALES    Revenue
--- ---------- ---------- ----------
T10
T07      23.95    1500200   35929790
T05       6.95     201440    1400008
T12      12.99     100001 1299012.99
T03      39.95      25667 1025396.65
T11       7.99      94123  752042.77
T13      29.99      10467  313905.33
T06      19.95      11320     225834
T02      19.95       9566   190841.7
T04      12.99      13001  168882.99
T09      13.95       5000      69750

TIT      PRICE      SALES    Revenue
--- ---------- ---------- ----------
T08         10       4095      40950
T01      21.99        566   12446.34

13 rows selected.

SQL>
SQL> drop table titles;

Table dropped.

SQL>
SQL>








2.4.Order By
2.4.1.Sorting Rows Using the ORDER BY Clause
2.4.2.Sort last name ascending
2.4.3.Sort last name descending
2.4.4.Sort one column ascending and another column descending
2.4.5.Sort by index ascending and descending
2.4.6.Reference alias name in order by clause
2.4.7.Order by a calculated column
2.4.8.Sort the rows by descending order with DESC appended to order by clause
2.4.9.Sort two columns with different ordering
2.4.10.Use a column position number in the ORDER BY clause
2.4.11.A SELECT with Ordering
2.4.12.Order the table on the employee's original salary (orig_salary)
2.4.13.Order by two columns
2.4.14.Add aggregate function in order by clause
2.4.15.Order by username
2.4.16.Combine order by clause with case statement
2.4.17.Order Your Email