2.4 ⁑Sorting Data

Once you've loaded your data you can sort it using:

\dtlsort [⟨replacement list⟩]{⟨criteria⟩}{⟨db-name⟩}{⟨handler⟩}

where ⟨db-name⟩ is the label that identifies the database. The ⟨criteria⟩ argument is a comma-separated list of column labels that indicate the sort order. For example, if you first want to sort on the surname column and then on the forenames column the ⟨criteria⟩ should be surname,forenames (make sure you don't have any unwanted spaces in the list). You can optionally add =⟨order⟩ after the column label where ⟨order⟩ is either ascending or descending. If omitted, ascending is assumed. For example, to sort in descending order, first by surname and then by forenames, the ⟨criteria⟩ should be:

surname=descending,forenames=descending

The ⟨handler⟩ argument is a control sequence that's used for the comparisons. The datatool package comes with four handlers:

A case-sensitive comparison:

\dtlcompare
A case-insensitive comparison:

\dtlicompare
English word-ordering comparison (as described by the Oxford Style Manual [74]):

\dtlwordindexcompare
English letter-ordering comparison:

\dtlletterindexcompare

The last two are intended for indexes. If you want any further details about those handlers, see the datatool user guide [95]. The first two handlers, \dtlcompare and \dtlicompare, are the ones you're most likely to need for administrative purposes. The datatool package provides convenient shortcuts:

\DTLsort [⟨replacement list⟩]{⟨criteria⟩}{⟨db-name⟩}

which uses \dtlcompare and

\DTLsort*[⟨replacement list⟩]{⟨criteria⟩}{⟨db-name⟩}

which uses \dtlicompare.

The optional argument ⟨replacement list⟩ is provided in case null values are encountered. You're unlikely to have null values if you load your data from a CSV file , but you may have null values if you use datatooltk to fetch data from a SQL database.

For example, suppose you want to sort your data according to the author column, but if there's a null value in that column then sort it by the title column, you would need to do

\DTLsort[title]{author}{books}

(where books is the label identifying your data.)

⚠

TeX isn't designed for data analysis, so sorting your data within your document in this way isn't very efficient. It's therefore better to sort your data before you load it in your document. This can be done using datatooltk's --sort option (in batch mode) or via the Tools→Sort menu item (in GUI mode). Alternatively, if you're fetching data from a SQL database, it's more efficient to append the ORDER BY statement to your SELECT statement.

Examples:

Fetch and sort data from a CSV file.
- Either:
```
\DTLloaddb{books}{booklist.csv}

\DTLsort{author}{books}
```
- Or use datatooltk:
  
  $ datatooltk --output books.dbtex --csv booklist.csv --sort author
  and then in your document:
  
  \DTLloaddbtex{\books}{books.dbtex}
Fetch and sort data from a SQL database:

$ datatooltk --output books.dbtex --sqluser sampleuser --sqldb samples --sql "SELECT * FROM books ORDER BY author"
and then in your document:

\DTLloaddbtex{\books}{books.dbtex}

⇦

⇧

⇨

This book is also available as A4 PDF or 12.8cm x 9.6cm PDF or paperback (ISBN 978-1-909440-07-4).