Chapter 2. Sorting Query Results

This chapter focuses on customizing how your query results look. By understanding how you can control and modify your result sets, you can provide more readable and meaningful data.

2.1. Returning Query Results in a Specified Order

Problem

You want to display the names, job, and salaries of employees in department 10 in order based on their salary (from lowest to highest). You want to return the following result set:

```	ENAME       JOB               SAL
----------  ---------  ----------
MILLER      CLERK            1300
CLARK       MANAGER          2450
KING        PRESIDENT        5000```

Solution

Use the ORDER BY clause:

```	1 select ename,job,sal
2   from emp
3  where deptno = 10
4  order by sal asc```

Discussion

The ORDER BY clause allows you to order the rows of your result set. The solution sorts the rows based on SAL in ascending order. By default, ORDER BY will sort in ascending order, and the ASC clause is therefore optional. Alternatively, specify DESC to sort in descending order:

````	select ename,job,sal`
`	  from emp`
`	 where deptno = 10`
`	 order by sal desc`

ENAME       JOB               SAL
----------  ---------  ----------
KING        PRESIDENT        5000
CLARK       MANAGER          2450
MILLER      CLERK            1300```

You need not specify the name of the column on which to sort. You can instead specify a number representing the column. The number starts at 1 and matches the items in the SELECT list from left to right. For example:

````	select ename,job,sal`
`	  from emp`
`	 where deptno = 10`
`	 order by 3 desc` ENAME JOB SAL ---------- --------- ---------- KING PRESIDENT 5000 CLARK MANAGER 2450 MILLER CLERK 1300 ...```

Get SQL Cookbook now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.