Sort (ORDER BY) - SQL Strategy - A site to master SQL while executing it on the Web

Learn how to sort data conditionally extracted with a SELECT statement.

1. How to write sort (ORDER BY)

--ENAME (employee name), JOB and SAL (salary) with 'SALESMAN' as JOB, sorted by SAL

SELECT	ENAME,JOB,SAL
FROM	EMP
WHERE	JOB = 'SALESMAN'
ORDER BY	SAL

■How to sort

To sort records, use ORDER BY, where ORDER BY is followed by the column name to be used as the sort key. To use multiple sort keys, use a comma-separated list of column names, such as ORDER BY SAL,DEPTNO.

■ORDER BY position

ORDER BY is written after the WHERE condition; if no WHERE condition is specified, the ORDER BY clause is written after the FROM clause.

■sort order

Normally sorted in ascending order, but can be sorted in descending order by adding the DESC keyword, as in ORDER BY SAL DESC. To explicitly sort in ascending order, add the ASC keyword.

ASC	ascending-order
DESC	descending-order

■Practice

Now for the Practice, I will give you two questions.

Practice (1/2) Questions

Display ENAME (employee name), JOB and SAL (salary) from the EMP table, sorted by SAL in descending order.

Practice (1/2) Answers

SELECT	ENAME,JOB,SAL
FROM	EMP
ORDER BY	SAL DESC

To sort in descending order, specify DESC.

Practice (2/2) Questions

Display ENAME (employee name) JOB (job) DEPTNO (affiliation No.) SAL (salary) from the EMP table, sorted by DEPTNO, SAL, except for JOB (job) 'PRESIDENT'.

Practice (2/2) Answers

SELECT	ENAME,JOB,DEPTNO,SAL
FROM	EMP
WHERE	JOB <> 'PRESIDENT'
ORDER BY	DEPTNO,SAL

When specifying multiple sort keys, separate them with "," (comma) after ORDER BY.

2. past question of the sort

Basic Information Technology Engineer AM Spring 2003 Q69

Question
Answer and Explanation

Which SQL statement is used to obtain Table B from Table A?
Table A

employee code	Name	Department Code	salary
10010	Ito Sachiko	101	200,000
10020	Saito Eiji	201	300,000
10030	Suzuki Yuichi	101	250,000
10040	Honda Kazu	102	350,000
10050	Yamada Goro	102	300,000
10060	Wakayama Mari	201	250,000

Table B

Department Code	employee code	Name
101	10010	Ito Sachiko
101	10030	Suzuki Yuichi
102	10040	Honda Kazu
102	10050	Yamada Goro
201	10020	Saito Eiji
201	10060	Wakayama Mari

A	SELECT * FROM TableA WHERE Department Code <> NULL
B	SELECT Department Code,employee code,Name FROM TableA
C	SELECT Department Code,employee code,Name FROM TableA GROUP BY Department Code
D	SELECT Department Code,employee code,Name FROM TableA ORDER BY Department Code

Answer

　D

Explanation

1. Check the items shown in the table after SELECT.
　First, A is removed from the candidate list because it makes all items Table.

2. focus on the item being sorted.
　Since it is sorted by Department Code, D in ORDER BY Department Code is Answer.