TeamForm Reports uses SQL which is a standard language for accessing and manipulating databases.
The SQL SELECT Statement
The SELECT statement is used to select data from a table.
The data returned is stored in a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, ... FROM table_name
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name
The SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfil a specified condition.
WHERE Syntax
SELECT column1, column2, ... FROM table_name WHERE condition
Example
The following SQL statement selects the firstname and surname of all the people from the country = ‘Mexico', in the people table:
SELECT firstname, surname FROM people WHERE country = 'Mexico'
For conditions, you must use single 'quotations’ rather than double
The SQL ORDER BY Clause
The ORDER BY clause is used to sort your columns of data by a particular ordering e.g. ASC ascending or DESC descending and even across multiple columns for more advanced sorting.
ORDER BY Syntax
SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column order
Example Descending order
The following SQL statement selects the firstname and surname of all the people from the country = ‘Mexico', in the people table and then sorts by surname in DESC (descending order):
SELECT firstname, surname FROM people WHERE country = 'Mexico' ORDER BY surname
You could also add the keyword ASC but in this case, the default behaviour is ascending order.
Example Ascending order
The following SQL statement selects the firstname and surname of all the people from the country = ‘Mexico', in the people table and then sorts by surname in ASC(ascending order - the default):
SELECT firstname, surname FROM people WHERE country = 'Mexico' ORDER BY surname
Example ORDER BY Multiple Columns
The following SQL statement selects the firstname and surname of all the people from the country = ‘Mexico', in the people table and then sorts by surname in DESC order and then firstname in ASC (ascending order - the default):
SELECT firstname, surname FROM people WHERE country = 'Mexico' ORDER BY surname DESC, firstname ASC
It is also possible to reference your columns numerically int he order they appear in the select statement e.g. ORDER BY 2 DESC, 1 ASC would achieve the same intended behaviour.
This might cause more confusion if you have a lot of columns in your SELECT statement/
Want to learn more SQL?
If you are interested in digging further into SQL there are a wealth of resources (articles, books, videos online) and your organisation may even have a learning platform with paid resources for you to learn from. In the mean time a useful basic cheatsheet can be found here:https://learnsql.com/blog/sql-basics-cheat-sheet/