1
00:00:00,000 --> 00:00:04,300
Hello, and welcome to sorting SELECT statement results sets.

2
00:00:04,300 --> 00:00:08,260
In this video, we will learn about some advanced techniques in retrieving data from

3
00:00:08,260 --> 00:00:12,815
a relational database table and sorting how the result set displays.

4
00:00:12,815 --> 00:00:14,500
At the end of this lesson,

5
00:00:14,500 --> 00:00:19,210
you will be able to describe how to sort the result set by either ascending or

6
00:00:19,210 --> 00:00:24,400
descending order and explain how to indicate which column to use for the sorting order.

7
00:00:24,400 --> 00:00:29,430
The main purpose of a database management system is not just to store the data,

8
00:00:29,430 --> 00:00:32,290
but also facilitate retrieval of the data.

9
00:00:32,290 --> 00:00:33,880
In its simplest form,

10
00:00:33,880 --> 00:00:38,065
a select statement is select * from table name.

11
00:00:38,065 --> 00:00:42,425
Based on our simplified library database model, in the table book,

12
00:00:42,425 --> 00:00:47,000
select * from book gives a result set of four rows.

13
00:00:47,000 --> 00:00:51,365
All the data rows for all columns in the table book are displayed.

14
00:00:51,365 --> 00:00:55,845
We can choose to list the book titles only as shown in this example,

15
00:00:55,845 --> 00:00:57,595
select title from book.

16
00:00:57,595 --> 00:01:00,910
However, the order does not seem to be in any order.

17
00:01:00,910 --> 00:01:02,440
Displaying the results set in

18
00:01:02,440 --> 00:01:05,875
alphabetical order would make the result set more convenient.

19
00:01:05,875 --> 00:01:09,195
To do this, we use the "order by" clause.

20
00:01:09,195 --> 00:01:12,050
To display the result set in alphabetical order,

21
00:01:12,050 --> 00:01:15,380
we add the order by clause to the select statement.

22
00:01:15,380 --> 00:01:20,430
The order by clause is used in a query to sort the result set by a specified column.

23
00:01:20,430 --> 00:01:26,115
In this example, we have used order by on the column title to sort the result set.

24
00:01:26,115 --> 00:01:30,070
By default, the result set is sorted in ascending order.

25
00:01:30,070 --> 00:01:35,690
In this example, the result set is sorted in alphabetical order by book title.

26
00:01:35,690 --> 00:01:38,965
To sort in descending order, use the key word" desc."

27
00:01:38,965 --> 00:01:42,400
The result set is now sorted according to the column specified,

28
00:01:42,400 --> 00:01:45,575
which is title, and is sorted in descending order.

29
00:01:45,575 --> 00:01:48,060
Notice the order of the first three rows.

30
00:01:48,060 --> 00:01:50,950
The first three words of the title are the same,

31
00:01:50,950 --> 00:01:54,595
so the sorting starts from the point where the characters differ.

32
00:01:54,595 --> 00:01:59,730
Another way of specifying the sort column is to indicate the column sequence number.

33
00:01:59,730 --> 00:02:03,495
In this example, select title, pages from book

34
00:02:03,495 --> 00:02:08,770
order by two, indicates the column sequence number in the query for the sorting order.

35
00:02:08,770 --> 00:02:11,670
Instead of specifying the column name pages,

36
00:02:11,670 --> 00:02:13,215
the number two is used.

37
00:02:13,215 --> 00:02:17,910
In the select statement, the second column specified in the column list is pages,

38
00:02:17,910 --> 00:02:21,505
so the sort order is based on the values in the pages column.

39
00:02:21,505 --> 00:02:26,375
In this case, the pages column indicates the number of pages in the book.

40
00:02:26,375 --> 00:02:31,400
As you can see, the result set is in ascending order by number of pages.

41
00:02:31,400 --> 00:02:35,880
Now you can describe how to sort the result set by either ascending or

42
00:02:35,880 --> 00:02:41,335
descending order, and explain how to indicate which column to use for the sorting order.

43
00:02:41,335 --> 00:02:44,230
Thanks for watching this video.