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.




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




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 Ctrl-click different columns to sort by those other columns, and then Ctrl-click to sort once more by state, and have a different order of cities within Alabama than seen above.   Sorting by state simply guarantees that all records for Alabama will appear before Alaska and so on.




If we choose View - Order we see that the sub-menu reports the state field is being used to sort the table.

Reversing Sort Order

Ctrl-clicking a sorted column's handle reverses the sort order.



Ctrl-clicking the column handle again in our example re-sorts the table window in descending order on that field.   For example, we can Ctrl-click on the state column handle again to sort 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-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-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-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.  




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 query in background.


See Also

Getting Started


User Interface Basics




Sorting Columns




Editing Tables


View - Filter


View - Order