Query - Not sorting list of ID number in output

LaTasha Jimenez 3

Greetings Community,

I am trying to figure out how to NOT have a listing of IDs sort in query output.  The list was copied from Excel with the criteria Constituent ID,"one of" but in the output the order of the list sorts in ascending order when in the #3 Sort section is blank.   The list re-sorting in RE in a different order than the original list causes some issues with comparison.    Just wondering if anyone have any tips on how to keep the output from not automatically sorting.


Thanks for any help.

LaTasha

JoAnn Strommen

How were the records sorted in Excel?  If you know that you could possibly select that field to sort the query.  Yes, RE seems to have a built in default of sorting by ID#. I've run into that issue also.

LaTasha Jimenez 3

The list does not have a particular sort order because it is created from another list in text (.txt) format.  Ideally, I would re-sort the excel list but the orignal text cannot be sorted so I am doing some manual searching to link data.  I was hoping there was a setting adjustment I have been over-looking to discontinue the auto sort.  Thanks anyways.


LaTasha 

LaTasha Jimenez 3

JoAnn Strommen:

How were the records sorted in Excel?  If you know that you could possibly select that field to sort the query.  Yes, RE seems to have a built in default of sorting by ID#. I've run into that issue also.

The list does not have a particular sort order because it is created from another list in text (.txt) format.  Ideally, I would re-sort the excel list but the orignal text cannot be sorted so I am doing some manual searching to link data.  I was hoping there was a setting adjustment I have been over-looking to discontinue the auto sort.  Thanks anyways.


LaTasha 

Heather MacKenzie

Could you open the .txt list into Excel and then sort there maybe?

LaTasha Jimenez 3

Heather MacKenzie:

 

Could you open the .txt list into Excel and then sort there maybe?

 

Hi Heather,

I normally copy the .txt to Excel but need to use the .txt document as the final output to provide to other individuals.  With the sorting order changing in RE and Excel linking the info that I am pulling from RE back to .txt is time consuming.  It would be nice if the RE Query output would not auto-sort.  Thanks for your question.

 

John Heizer

LaTasha Jimenez:

Greetings Community,

I am trying to figure out how to NOT have a listing of IDs sort in query output.  The list was copied from Excel with the criteria Constituent ID,"one of" but in the output the order of the list sorts in ascending order when in the #3 Sort section is blank.   The list re-sorting in RE in a different order than the original list causes some issues with comparison.    Just wondering if anyone have any tips on how to keep the output from not automatically sorting.


Thanks for any help.

LaTasha

Unfortunately this is standard behavior for any SQL database.  If the Query is not given any specific instructions on how to sort the output then it returns records in the order of the database's default sort order (based on various indexes in the database), which happens to be Constituent ID.  There's no way I know of to override this in RE, so I think you're stuck sorting the data in Excel before pasting the criteria into RE.  And remember that RE considers Constituent ID to be text data, not numeric, so 12345 sorts above 2, not below.

Alan French

It should be pretty easy to sort your query output back into the original order from the .txt file, if you've got IDs in both lists. Open both in Excel, then in the column next to your new data use the Match function to look up each ID against your old list and return its row number. Then all you have to do is sort your new data by the column with your match formula and it should end up in the same order as your original data, assuming that each ID is only in the list once.

LaTasha Jimenez 3

Alan French:

It should be pretty easy to sort your query output back into the original order from the .txt file, if you've got IDs in both lists. Open both in Excel, then in the column next to your new data use the Match function to look up each ID against your old list and return its row number. Then all you have to do is sort your new data by the column with your match formula and it should end up in the same order as your original data, assuming that each ID is only in the list once.

Thanks so much Alan for your Great suggestion!  I will try it see if it helps.

LaTasha Jimenez 3

John Heizer:

 

LaTasha Jimenez:

Greetings Community,

I am trying to figure out how to NOT have a listing of IDs sort in query output.  The list was copied from Excel with the criteria Constituent ID,"one of" but in the output the order of the list sorts in ascending order when in the #3 Sort section is blank.   The list re-sorting in RE in a different order than the original list causes some issues with comparison.    Just wondering if anyone have any tips on how to keep the output from not automatically sorting.


Thanks for any help.

LaTasha

Unfortunately this is standard behavior for any SQL database.  If the Query is not given any specific instructions on how to sort the output then it returns records in the order of the database's default sort order (based on various indexes in the database), which happens to be Constituent ID.  There's no way I know of to override this in RE, so I think you're stuck sorting the data in Excel before pasting the criteria into RE.  And remember that RE considers Constituent ID to be text data, not numeric, so 12345 sorts above 2, not below.

 

Thanks John for the explanation on why query auto sorts.  You have helped me see that I am not crazy for not finding any setting fix.  I have been searching RE for a while and eventually gave up.  Thanks much!!


LaTasha