Title: Single Table sorting not working Last modified: March 9, 2021 --- # Single Table sorting not working * Resolved [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/) * I have a single table. the index is on the column TERM (char (10)). If holds the term on an officer, example. 1964-1965, 1965-1966, etc.. However I set the default order/by in the data publisher (1,desc or 1,asc), but it has not affect on the sorting of the table. I am looking to sort this from the newest to the oldest. Since I entered the data from oldest to newest, that is the results I get. The page is live and the link is posted above. * Thank you in advance for your assistance in this matter. - This topic was modified 5 years, 3 months ago by [websgt](https://wordpress.org/support/users/websgt/). - This topic was modified 5 years, 3 months ago by [websgt](https://wordpress.org/support/users/websgt/). * The page I need help with: _[[log in](https://login.wordpress.org/?redirect_to=https%3A%2F%2Fwordpress.org%2Fsupport%2Ftopic%2Fsingle-table-sorting-not-working%2F%3Foutput_format%3Dmd&locale=en_US) to see the link]_ Viewing 15 replies - 1 through 15 (of 19 total) 1 [2](https://wordpress.org/support/topic/single-table-sorting-not-working/page/2/?output_format=md) [→](https://wordpress.org/support/topic/single-table-sorting-not-working/page/2/?output_format=md) * Plugin Author [Passionate Programmer Peter](https://wordpress.org/support/users/peterschulznl/) * (@peterschulznl) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14154022) * Hi [@websgt](https://wordpress.org/support/users/websgt/), * Can you please change your default order by from: 1,desc to: 0,desc The column index is zero based. It looks like this is the first column… * Let me know, Peter * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14155877) * Peter, Thank you for your quick reply. * 1. I should have known that.. 2. I tried it both way, 0,desc and 0,asc. The table results are unchanged. * Thanks, Steve * Plugin Author [Passionate Programmer Peter](https://wordpress.org/support/users/peterschulznl/) * (@peterschulznl) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14155998) * Hi Steve, * Your order by looks like this: 0,asc; * Please remove the ; character like this: 0,asc * Thanks, Peter * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14156636) * Peter, * I had tried it both asc and desc with and without the “;”, as I read somewhere that might be required. Just to be sure, I just tried it again, without the “;” and the results are unchanged. I also tried it asc and desc. One of them should have changed the results. * I’m open for suggestions. * Thanks. Steve * [rvwill48](https://wordpress.org/support/users/rvwill48/) * (@rvwill48) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14156896) * Can you just sort using just the first four characters of the TERM field? This will definitely work. My suspicion is that the presence of the minus sign in the term of office is somehow causing MySQL to convert the field into a numeric calculation whereupon the result of each row is -1. This would explain no change when sorting ASC or DESC. Just a thought. * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14157922) * [@rvwill48](https://wordpress.org/support/users/rvwill48/) How do I set that up to use the first 4 Characters? The field is set to Char (9). - This reply was modified 5 years, 3 months ago by [websgt](https://wordpress.org/support/users/websgt/). * [rvwill48](https://wordpress.org/support/users/rvwill48/) * (@rvwill48) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14158033) * try ORDER BY LEFT(TERM,4) DESC * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14158312) * [@rvwill48](https://wordpress.org/support/users/rvwill48/), Thank you for your reply. * hmm.. I don’t seem to have access to the underlaying SQL in this plugin. I would think that would need to be entered in the Data Publisher section, but it requires Column, asc|desc. I don’t see where I can use the LEFT clause for TERM. * Regards, Steve * Plugin Author [Passionate Programmer Peter](https://wordpress.org/support/users/peterschulznl/) * (@peterschulznl) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14159410) * Hi Steve and Richard, * There is no way to use SQL functions in the order by of a publication. But there are alternatives: (1) Use a view and add your order by to your view (2) Use Data Projects to create a read-only Data Form (requires a premium license) * Does this help? * Best regards, Peter * [rvwill48](https://wordpress.org/support/users/rvwill48/) * (@rvwill48) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14159545) * Ok Peter, thanks for clarifying. Could Steve’s issue have anything to do with webpage caching then, since he’s changing the sort order in the publication but sees no effect? How is the ‘order by’ setting applied in your plugin? Richard * Plugin Author [Passionate Programmer Peter](https://wordpress.org/support/users/peterschulznl/) * (@peterschulznl) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14161332) * Good point Richard! * Current order by = 0,desc But the table is not ordered on that column. * [@websgt](https://wordpress.org/support/users/websgt/) Is this a view which has its own order by? * Thanks, Peter * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14162586) * Peter, * It is one table with one view. * Here is the SQL for the create table: * CREATE TABLE MCL_Officers (Term char(10) NOT NULL ,Commandant char(100) NULL, Sr_Vice_Commandant char(100) NULL ,Jr_Vice_Commandant char(100) NULL ,Judge_Advocate char(100) NULL ,Adjutant char(100) NULL ,Paymaster char(100) NULL ,Sergeant_At_Arms char(100) NULL ,Chaplain char(100) NULL ,Aide_de_Camp char(100) NULL ,Jr_PastCommandant char(100) NULL ,WebSergeant char(100) NULL ,Historian char(100) NULL ,Public_Affairs char(100) NULL ,PRIMARY KEY (Term) ) ENGINE MyISAM DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci; * CREATE UNIQUE INDEX `MCL_INDX` ON `MCL_Officers` (`Term`); * On the data publisher I have the columns set for: Term,Commandant,Sr_Vice_Commandant, Jr_Vice_Commandant,Judge_Advocate,Adjutant,Paymaster,Sergeant_At_Arms,Chaplain, Aide_de_Camp,Jr_PastCommandant,WebSergeant,Historian * (it is essentially everything except the last field which is public affairs ( not currently needed.) * Output: responsive number of columns: 1 * In all its one table with 1 view. Default Order/By: 0,desc * Term is the first column, hence 0 for the order by, and I have tried both asc and desc to see if it make a difference. * The page is live and I put the link in the initial post to see the out put (second table down, the first table is static) * I have gone through the data designer, project templates, data projects and data publisher and reviewed all the options. Did I miss something? * Thanks for your help. * [rvwill48](https://wordpress.org/support/users/rvwill48/) * (@rvwill48) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14163208) * Looking at your create table statement I notice that: 1. Your database engine is specified as ISAM, why is this? 2. You specify a Primary Key on the TERM column and 3. Then go on to specify a unique index on the same column. * A primary key column is by definition an unique index, so it appears you are creating a second unique index on a primary key column. I’ve no idea what effect this will have on the behaviour of the database table when sorting. * To reorder your table rows you’ll need administrative access to the database. I would suggest using the ALTER TABLE sql command to reorder the rows as follows: * ALTER TABLE ‘MCL_Officers’ ORDER BY ‘TERM’ DESC; * This will physically rearrange the records into the order you want and they’ll stay that way until you add a new record or delete an existing one, then you’ll need to rerun the above ALTER TABLE command again if by then you haven’t fixed the sorting issue. * Best wishes, Richard * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14164719) * [@rvwill48](https://wordpress.org/support/users/rvwill48/), * Well I changed the table type to InnoDB and dropped the index. To do this I had to drop the table. I exported the records as a CSV , of course I did not realize that import only supports SQL, and it only exported 10 records :(. Luckily I had an older list of the officers (up to 2014), and and inserted the appropriate SQL statements and got most of it imported. * Regardless, this change did not change the order of the records. * Thread Starter [websgt](https://wordpress.org/support/users/websgt/) * (@websgt) * [5 years, 3 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/#post-14164773) * [@peterschulznl](https://wordpress.org/support/users/peterschulznl/) I am adding a id column (rec_ID), I have it as mandatory, and key. How do I set it to auto- increment? Viewing 15 replies - 1 through 15 (of 19 total) 1 [2](https://wordpress.org/support/topic/single-table-sorting-not-working/page/2/?output_format=md) [→](https://wordpress.org/support/topic/single-table-sorting-not-working/page/2/?output_format=md) The topic ‘Single Table sorting not working’ is closed to new replies. * ![](https://ps.w.org/wp-data-access/assets/icon-256x256.png?rev=3299063) * [WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards](https://wordpress.org/plugins/wp-data-access/) * [Support Threads](https://wordpress.org/support/plugin/wp-data-access/) * [Active Topics](https://wordpress.org/support/plugin/wp-data-access/active/) * [Unresolved Topics](https://wordpress.org/support/plugin/wp-data-access/unresolved/) * [Reviews](https://wordpress.org/support/plugin/wp-data-access/reviews/) ## Tags * [sorting](https://wordpress.org/support/topic-tag/sorting/) * 19 replies * 3 participants * Last reply from: [websgt](https://wordpress.org/support/users/websgt/) * Last activity: [5 years, 2 months ago](https://wordpress.org/support/topic/single-table-sorting-not-working/page/2/#post-14177766) * Status: resolved