There are quite a few other taxa that also have this problem. I assume it's something to do with the nameserver, no?

Test using this query:

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
  RIGHT(ITN.SORT_ORDER,8) = '00000000' AND
  TLI.SORT_CODE IS NOT NULL

Hi Charles

Needs further investigation, but the key thing to note is that according to the TSD when building the Index_Taxon_Name table, the sort_order is derived from:

Sort Order from taxon_group table

&

the Sort_code from the taxon_list_item table using the recommended_taxon_list_item_key from the nameserver.

So in the case of TLIk: NBNSYS0000002090 'Hirundo atrocaerulea'

- the sort order from the taxon_group table is '106' (so far so good)

- the recommended_taxon_list_item_key from the Nameserver table is 'NHMSYS000053396'

- the sort_code for 'NHMSYS000053396' in the Taxon_list_item table is 'null' (not so good)

hence this particular species ends up with a sort_order of '10600000000' in the Index_Taxon_Name table.

Try running the following query instead:

USE NBNDATA;
SELECT
  ITN.TAXON_LIST_ITEM_KEY,
  ITN.COMMON_NAME,
  ITN.PREFERRED_NAME,
  ITN.SORT_ORDER,
  TXG.SORT_ORDER,
  TLI2.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
INNER JOIN
    NAMESERVER N ON
    TXV.TAXON_VERSION_KEY = N.INPUT_TAXON_VERSION_KEY
INNER JOIN
    TAXON_LIST_ITEM TLI2 ON
    N.RECOMMENDED_TAXON_LIST_ITEM_KEY = TLI2.TAXON_LIST_ITEM_KEY
WHERE
  TXG.TAXON_GROUP_NAME = 'bird' AND
  ITN.SORT_ORDER = '10600000000'

Hope that makes sense.

It's my first delve into this particular area and i'm not sure why these taxon_list_item_keys don't have sort_orders.

John from the NHM may be able to shed some light on this?

Additional Note: I ran the above query in a 6.9.3 database. However, when I ran a query to find all the rows in Taxon_List_Item table with null sort codes (that are also recommended_taxon_list_item_keys in the Nameserver) in the most recent copy of the dictionary supplied by the NHM there are only 14 entries that potentially cause problems. However, I'll double check this in an updated copy of the Recorder 6 dictionary and also email John).

Kind regards,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC