Sorting Columns

Ctrl-clicking a column handle will sort the table window display for all fetched records by that field.   Clicking the column handle again will sort the table window in reverse order by that field.   Keep in mind we are not sorting data in the table itself: as with all high end database systems, table data within Manifold is always stored in unsorted form.   We are only sorting the display of the table data in the table window.

 

Sorting by ctrl-clicking a column head only sorts the records displayed in the window.  That will be all records in the table for tables stored in the .map project, or for tables from external data sources whatever is the number of records set to be displayed (which could be all records) in the Tools - Options dialog.    

 

If we want to sort a table window for a table stored in an external data source to sort all records, first load the table window with all records.  If the table window has been told to show fewer than all records, in the Info pane press the Fetch All button to load the table window with all records.

 

 Right-clicking a column handle provides Order Ascending and Order Descending commands to sort the table in ascending or descending order.   These commands duplicate Ctrl-click functionality as menu commands, to help new users who have yet to read this topic.   Experienced users will Ctrl-click a column handle to sort the table.  

 

 

The table window above shows a subset of cities taken from a collection of the one thousand largest cities in the US.   Ctrl-clicking the state column handle sorts the table window by state name, in ascending order.   

 

 

The table window will be sorted and scrolled to keep the whatever record the cursor is on in view, scrolling down to records with California as the state, since the cursor was on the row for Napa, California.  

 

 

We can scroll the table to the top using the scroll bar or by using the Ctrl-Home keyboard shortcut (Ctrl-End to jump to the bottom)  to see the illustration as shown above, with records for Alabama at the top of the table..  

 

Important:  The above sorts the table by state, but within each state, records are not ordered.   We can clear the sort order and then Ctrl-click to sort once more by state, and we might get a different order of cities within Alabama than seen above.  

 

Sorting by state simply guarantees that all records for Alabama will appear before Alaska, all records for Alaska will appear before Arizona, and so on.  Within each state, records might appear in a different order every time we sort by state.

 

 

If we choose View - Order we see that the sub-menu reports the state field is being used to sort the table.   The Clear Order command seen in the menu above will remove the sort order by that state, but will leave the table as displayed.   When tables are assumed to have no sort order unless one is commanded, it doesn't matter in what order the records are shown.

 

 

Once we sort by state, if we would like to sort records within each state, for example, by city, we can Shift-Ctrl-click the city column header.  

 

 

That will sub-sort the table by city as well as by state.

 

A Ctrl-click is a command to sort the table by the clicked field, replacing any previous sort order.   A Shift-Ctrl-click is a command to add a sort on the clicked field to any existing sort order.  The net effect is to take whatever is the existing sort order and to add a sub-sort by the Shift-Ctrl-clicked field.

 

We can Shift-Ctrl-click on as many fields as we like, to add sorting by additional fields.  

Reversing Sort Order

Ctrl-clicking a sorted column's handle when there is a sort on more than one column, like the illustration above, simply sorts by that column again, replacing the sort by multiple fields.

 

 

If we Ctrl-click the state column handle...

 

 

That replaces the prior sort using multiple columns to a simple sort on the state column.

 

 

Ctrl-clicking the state column handle again re-sorts the table window in descending order on that field.   That sorts the table by state name in reverse order, so records for the state of Wyoming will come first.

 

 

That re-sorts the table window so records with Wyoming as the value for the state field come first.   

 

 

Ctrl-clicking on another column handle sorts the table window by that field, discarding any previous sort order on any other column.  For example, we can Ctrl-click on the city field.   

 

 

That resorts the table window display so records with a city value beginning with A come first.

 

 

Whenever we Ctrl-click a column head that sorts the table by that column and clears the previous sort order.  

 

 

Ctrl-clicking on the state field will once again to sort the table window by state.   

Sub-sorting by a Second Field

Suppose we have sorted the table window by one field, such as state, and now we want to sort within that sort so that records are ordered first by state and then within the state from largest to smallest population.   Easy: after clicking on the state column handle to sort the display by state, then Shift-Ctrl-click on the population column handle to sort within each state grouping by population.  The Shift modifier tells Manifold not to abandon the previous sort but to add another field to the sort order.  

 

 

In the example above, after clicking on the state column handle to sort by state, we Shift-Ctrl-click on the population column handle to further sub-sort by the population field.

 

 

The default sort is ascending, within Alabama from lowest population in Phenix City to highest population in Birmingham.

 

 

To change that to a descending sort so that the cities within Alabama are sub-sorted from highest population to lowest population we Shift-Ctrl-click again on the population column handle.

 

 

That resorts the table window so first, all records are sorted by state and then second, records within each state are sorted from highest population to lowest population.  

 

 

If we choose View - Order we see that the sub-menu reports the state field is being used to sort the table with a sub-sort by the population field.   The sub-menu reports the fields being used in their sort and then sub-sort priority.

 

We can continue Shift-clicking additional fields to add yet more sub-sorts if we liked.   For example, suppose our table also had a Zip field giving zip code tabulation areas (like regions encompassing each postal code area) for each city in Alabama.   Suppose that there was not just one record that had Birmingham as the city and Alabama as the state, but ten such records, each one of which had a different value in the Zip field.   We could Shift-click on the Zip column handle and that would sort the table window so that all the Birmingham records were further sub-sorted by Zip.

 

We can also choose sort order for a table window by using the View - Order command.  The first field chosen using View - Order sorts the table while additional fields chosen using View - Order will add sub-sorts as we had Shift-clicked their column handles.

 

 

In the illustration above we have chosen View - Order - Clear Order to clear all sort order, and then in the View - Order submenu we have clicked on the city field.

 

 

The table display is sorted as if we had Ctrl-clicked the city column head.

NULLs Always Sort Last

Ctrl-clicking column headers in a table is a fast, interactive way for quick visual results.  It is different from using the ORDER BY clause in a query.   When Ctrl-clicking column headers, NULLs will always appear last in the sorted list.  NULLs are not values: they are indications that values in those cells are unknown.  Manifold therefore provides a column sorted on all values, and then lists all NULLs afterwards.

 

That is different than how ORDER BY works in Manifold when using SQL.  The ORDER BY clause is one of those places in SQL where the notion of a NULL as an unknown value meets established practice.  The SQL standard allows each DBMS to decide whether it considers NULLs as coming before, that is, the lowest value, or coming after, that is, the highest value, all other values in sort order.  

 

When running SQL using ORDER BY, Manifold is like Oracle, DB2 and PostgreSQL in treating NULLs as the highest value.  Using ORDER BY in default, ascending order to sort a text column that contains names like Ann, Bob and Zorro plus a NULL record will result in Ann, Bob, Zorro, NULL order.   Using ORDER BY with the DESC (descending) keyword will result in NULL, Zorro, Bob, Ann order.   Note that Microsoft Access, Microsoft SQL Server and MySQL treat NULLs as the lowest value for sort order.  

 

Notes

Reapply Order - The Reapply Order choice refreshes the table window to apply whatever are the current sort order settings.   It is used in cases where the data in tables might come from external data sources or otherwise might be have been changed by a program or by a query running in background.

 

16 Gigabyte Total Sort Criteria -  This is a theoretical limit that nobody will ever encounter, but it should be noted, just in case.   An individual cell in Manifold can contain up to 2 gigabytes of data.   When we sort a column, and then further sub-sort by other columns, the total field size of the sorted columns used as sort criteria has to be under 16 gigabytes for any one record involved in the sort.    If each column used for sorting / sub-sorting criteria has the maximum of 2 gigabytes in its cells for any one record, we therefore can do sorts that use up to eight columns used as sub-sort criteria.  If columns have less than 2 gigabytes in each cell, then we can use more columns as sub-sorting criteria.

 

16 gigabytes for sorting criteria is an absurdly large amount of data, since having individual records that are over 16 gigabytes in size for just one record is very rare.  Given typical data sizes in fields used for sorts, such as Name, Population, and similar, there is no effective limit on how many columns we can sub-sort by.  However, if you do have tables with individual records that are large than 16 GB in size, do not expect to sort the table by sub-sorting on all of the columns in such records at once.

 

Use SQL with Big Tables - Sorting table windows by Ctrl-clicking column heads is a great convenience when interactively browsing smaller tables, but it is a low performance way of browsing big tables because interactive sorts are slow with really big tables.  SQL can sort big tables much faster.   If we want to get an idea what are the top hundred or so records by given sort order in some multimillion or billion record table, it is much faster to use a simple line of SQL:

 

SELECT * FROM [table_name] ORDER BY [field_of_interest] FETCH 100;  

 

Toss in an ASC or DESC after the field name to tell ORDER BY to sort by ascending or descending order to get the bottom 100 or top 100 by sort order.

 

Using SQL can also save us from having to scroll through endless rows of <NULL> values by adjusting the query:

 

SELECT * FROM [table_name] WHERE [field_of_interest] IS NOT NULL

   ORDER BY [field_of_interest] DESC FETCH 100;  

 

That gets us what we want to see right away.

 

See Also

Getting Started

 

User Interface Basics

 

Tables

 

Sorting Columns

 

Filters

 

Editing Tables

 

View - Filter

 

View - Order