Sorting results in SQL

Sorting results can be achieved by using a modifier command at the end of the SQL querry

#load the extension
%load_ext sql 
#connect to the database
%sql sqlite:///mydatabase.db 

'Connected: @mydatabase.db'

The results can be sorted with the command ORDER BY

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]

Let's show an example where we extract the fruits that are either yellow or red

%sql SELECT * FROM tutyfrutty WHERE color = "yellow" OR color = "red" 

 * sqlite:///mydatabase.db
Done.

Ascending sort

%sql SELECT * FROM tutyfrutty WHERE color = "yellow" OR color = "red" ORDER BY kcal ASC

 * sqlite:///mydatabase.db
Done.

descending sort

%sql SELECT * FROM tutyfrutty WHERE color = "yellow" OR color = "red" ORDER BY kcal DESC

 * sqlite:///mydatabase.db
Done.

Sort by multiple columns

You can sort by more than one column. Just specify multiple columns in the ORDER BY keyword. In the example, we will sort alphabetically on the color column first and sort alphabetically on the fruit column

%sql SELECT * FROM tutyfrutty ORDER BY color, fruit ASC

 * sqlite:///mydatabase.db
Done.