Re: Dodgy sort orders?
I recently noticed a few of our bird records appearing out-of-order (i.e., not in the correct taxonomic order) in reports. On closer inspection, I found that many species have a SORT_ORDER of 10600000000, which would indicate that these species don't have a SORT_CODE in the TAXON_LIST_ITEM table. However, it seems that they do have SORT_CODE, but for some reason, they're not being correctly generated when building the INDEX_TAXON_NAME.
Try running the following SQL to see what I mean:
USE NBNData;
SELECT
ITN.TAXON_LIST_ITEM_KEY,
ITN.COMMON_NAME,
ITN.PREFERRED_NAME,
ITN.SORT_ORDER,
TXG.SORT_ORDER,
TLI.SORT_CODE
FROM
INDEX_TAXON_NAME ITN
INNER JOIN
TAXON_LIST_ITEM TLI ON
ITN.TAXON_LIST_ITEM_KEY = TLI.TAXON_LIST_ITEM_KEY
INNER JOIN
TAXON_VERSION TXV ON
TLI.TAXON_VERSION_KEY = TXV.TAXON_VERSION_KEY
INNER JOIN
TAXON_GROUP TXG ON
TXV.OUTPUT_GROUP_KEY = TXG.TAXON_GROUP_KEY
WHERE
TXG.TAXON_GROUP_NAME = 'bird' AND
ITN.SORT_ORDER = '10600000000'
Here's the first few rows I get back (copy and paste into a text file, save it as a .CSV then open in Excel for optimal viewing):
TAXON_LIST_ITEM_KEY,COMMON_NAME,PREFERRED_NAME,SORT_ORDER,SORT_ORDER,SORT_CODE
NBNSYS0000001839,Aquila heliaca,Aquila heliaca,10600000000,106,85
NBNSYS0000002172,Aquila heliaca,Aquila heliaca,10600000000,106,62
NBNSYS0000002012,Threskiornis aethiopicus subsp. aethiopicus,Threskiornis aethiopicus subsp. aethiopicus,10600000000,106,118
NBNSYS0000002232,Columba palumbus subsp. azorica,Columba palumbus subsp. azorica,10600000000,106,122
NBNSYS0000001967,Gavia immer subsp. immer,Gavia immer subsp. immer,10600000000,106,73
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital