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.

 

eg_table_columns_sort01_00.png

 

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.   

 

eg_table_columns_sort01_01.png

 

Sorting the table window by state means that all records displayed by the window with a state value of Alabama will appear before those from Alaska, which all will appear before those from Arizona and so on.   tech_ravi_sm.png

 

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.

 

eg_table_columns_sort01_01b.png

 

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.

 

eg_table_columns_sort01_01a.png

 

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.  

 

eg_table_columns_sort01_01c.png

 

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.

 

eg_table_columns_sort01_01d.png

 

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

 

eg_table_columns_sort01_01e.png

 

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

 

eg_table_columns_sort01_02.png

 

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.

 

eg_table_columns_sort01_02a.png

 

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

 

eg_table_columns_sort01_03.png

 

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.   

 

eg_table_columns_sort01_03a.png

 

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

 

eg_table_columns_sort01_04.png

 

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

 

eg_table_columns_sort01_04a.png

 

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.  

 

eg_table_columns_sort01_05.png

 

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.

 

eg_table_columns_sort01_06.png

 

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.

 

eg_table_columns_sort01_07.png

 

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.  

 

eg_table_columns_sort01_07a.png

 

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.

 

eg_table_columns_sort01_07b.png

 

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.

 

eg_table_columns_sort01_08.png

 

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.

 

ico_nb_arrow_blue.png 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.

 

See Also

Getting Started

 

User Interface Basics

 

Tables

 

Sorting Columns

 

Filters

 

Editing Tables

 

View - Filter

 

View - Order