This demo will use the iris dataset.

This page shall incorporate the additional filtering statements ORDER BY, DISTINCT and WHERE on top of the already-established SQL commands.

ORDER BY allows output to be ordered by ascending (ASC) or descending (DESC), based on one feature or multiple.

Example: What are the top ten longest petal lengths in the iris dataset?

sqldf("
  SELECT `Petal.Length` FROM iris
  ORDER BY `Petal.Length` DESC
  LIMIT 10;
")

##    Petal.Length
## 1           6.9
## 2           6.7
## 3           6.7
## 4           6.6
## 5           6.4
## 6           6.3
## 7           6.1
## 8           6.1
## 9           6.1
## 10          6.0

Example: What are the species of the three shortest sepal widths?

sqldf("
  SELECT Species FROM iris
  ORDER BY `Sepal.Width` ASC
  LIMIT 3;
")

##      Species
## 1 versicolor
## 2 versicolor
## 3 versicolor

Distinct is used when you want to return unique values, it is one of the most often-used statements:

Example: What iris species exist in the iris dataset?

sqldf("SELECT DISTINCT Species FROM iris;")

##      Species
## 1     setosa
## 2 versicolor
## 3  virginica

Example: What are 10 unique combinations of iris species and petal lengths ordered by the longest sepals?

sqldf("
  SELECT DISTINCT Species, `Petal.Length` FROM  iris
  ORDER BY `Sepal.Length` DESC
  LIMIT 10;
")

##       Species Petal.Length
## 1   virginica          6.4
## 2   virginica          6.7
## 3   virginica          6.9
## 4   virginica          6.6
## 5   virginica          6.3
## 6   virginica          6.1
## 7   virginica          5.9
## 8  versicolor          4.7
## 9  versicolor          4.9
## 10  virginica          5.7

Finally, WHERE allows you to set conditionals. The syntax for this are as follows:

Symbol	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

The WHERE statement gives you a lot of control over your data. This statement allows much more complicated questions be answered.

Example: What are the species and petal lengths of iris’ which have petal widths greater than 1.5?

sqldf("
  SELECT Species, `Petal.Width` FROM iris
  WHERE `Petal.Width` > 2.2;
")

##      Species Petal.Width
## 1  virginica         2.5
## 2  virginica         2.5
## 3  virginica         2.4
## 4  virginica         2.3
## 5  virginica         2.3
## 6  virginica         2.3
## 7  virginica         2.3
## 8  virginica         2.4
## 9  virginica         2.4
## 10 virginica         2.3
## 11 virginica         2.3
## 12 virginica         2.5
## 13 virginica         2.3
## 14 virginica         2.3

Example: How many data points have a petal width of exactly 2?

sqldf("
  SELECT COUNT(Species) FROM iris
  WHERE `Petal.Width` = 2;
")

##   COUNT(Species)
## 1              6