1

Topic: Displaying First County Records

I have written a report which reports the first/last records for each species in the database - the SQL runs fine in SQL Server Management Studio, but when I apply it to Recorder as an XML, it appears to completely ignore the ORDER BY command - anyone know what I've done wrong?

The Report is attached

Craig

Post's attachments

SER001R03 First County Records.xml 2.17 kb, 4 downloads since 2019-11-07 

You don't have the permssions to download the attachments of this post.
Craig Slawson
Staffordshire Ecological Record

2

Re: Displaying First County Records

Could it be related to this: https://forums.nbn.org.uk/viewtopic.php?id=4845

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

3

Re: Displaying First County Records

Does look to be related, however, running the SQL in SQL Management Studio, the ORDER_BY is recognised and interpreted correctly (but not when run through Recorder)

Craig

Craig Slawson
Staffordshire Ecological Record

4

Re: Displaying First County Records

AFAIK Recorder inserts the results into a temporary table and expects the sort to be maintained (it isn't guaranteed), hence why it works okay in management studio.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

5

Re: Displaying First County Records

Hi

Just noticed this post. You need to apply the sort in the xml report using OutputTableOrderby eg. 

</Where>
<OutputTableOrderby name="by default" SQL="ORDER BY Matched_List,Taxa_Input"/>
</SQL>

Mike Weideli
Littlefield Consultancy - IT Consultants

6

Re: Displaying First County Records

This looks a useful report, please would you share it, assuming you got it to work?
many thanks

Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership