# Sort Dataset Based on Two Column **URL:** \ **Category:** Ignition\ **Tags:** python, scripting, vision, database\ **Created:** [June 19, 2024, 3:40pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105 "2024-06-19T15:40:52Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 3:40pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/1 "2024-06-19T15:40:52Z")
Hello everyone, I’m trying to sort a dataset based on two columns and tried following another post linked here to get the desired outcome, but I wasn’t successful. I was wondering if anyone has any insights/recommendations? > [@Sort Dataset on two columns](https://forum.inductiveautomation.com/t/sort-dataset-on-two-columns/29374): > > Is there a way to sort a dataset on two different columns. The data in the dataset is the result of a SQL query. Lets say i have a dateset with 10 columns. I have a column that has a flag that is set for example either UP or Down. Another column has a Sequence number that is unique for the two different types. So in the data I have 10 UPs and 10 Downs for a total of 20 rows. I want to sort first on the Type and get all my UPs, then within each type I want to sort by the sequence number so i end … For reference, I wrote the code below as part of my if-else statement. It sorts the table based on one column but doesn’t work when I add the second column: ```python activeOrderDataset = system.dataset.sort(activeOrderDataset,'Col1') activeOrderDataset = system.dataset.sort(activeOrderDataset,'Col2') pyActiveOrderDataSet = system.dataset.toPyDataSet(activeOrderDataset) ``` ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [June 19, 2024, 3:49pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/2 "2024-06-19T15:49:50Z")
Do you need to keep it in dataset format, or would python types (a list of objects) be acceptable ? edit: This will keep it in dataset format anyway: ```python return system.dataset.toDataSet( list(value.columnNames), sorted( system.dataset.toPyDataSet(value), key=lambda row: (row[col_1], row[col_2]) ) ) ``` Note that `col_1` and `col_2` can be either column names or indices ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 19, 2024, 4:16pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/3 "2024-06-19T16:16:18Z")
If this is for a binding, you might find my [orderBy()](https://www.automation-pros.com/toolkit/doc/iteration.html#orderby) expression function most convenient. ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 4:57pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/4 "2024-06-19T16:57:04Z")
Is this supported in Ignition 8.1? I will give it a try. ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 4:59pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/5 "2024-06-19T16:59:30Z")
Do you have an example of how it can be used? or example of its key expression? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 19, 2024, 5:05pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/6 "2024-06-19T17:05:59Z")
Sure: ```python orderBy( {path.to.activeOrderDataset}, it()['Col1'], it()['Col2'] ) ``` As an expression binding on the property where you wish the ordered dataset to be placed. ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 5:12pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/7 "2024-06-19T17:12:54Z")
Thank you!!! Any kind of import to happen before using OrderBy()? Do I need to install a module? Assuming we have to import the command? Cause I just cannot find any example or description for it. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [June 19, 2024, 5:17pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/8 "2024-06-19T17:17:19Z")
here: [Ignition Third-Party Module Showcase](https://inductiveautomation.com/moduleshowcase/module/automation-professionals-llc-integration-toolkit) ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 5:20pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/11 "2024-06-19T17:20:10Z")
Thank you! I have imported this and did gateway reset. It's just I don't know how to get it into Python. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 19, 2024, 5:20pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/12 "2024-06-19T17:20:35Z")
You don't, its an expression. Use an expression binding ---
**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\ **Post date:** [June 19, 2024, 5:20pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/13 "2024-06-19T17:20:39Z")
It isn't a python function, but an expression. ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 5:27pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/14 "2024-06-19T17:27:33Z")
I have tried this and it worked. The only issue is that it works only if you have a table with one page once the data gets bigger and the table goes into the 2nd page it won't work for the 2nd page and the pages afterward. Thank you!! ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 5:55pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/15 "2024-06-19T17:55:15Z")
Your solution worked better cause when you have a table with multiple pages, the expression binding does not work. So I used your version, those two columns were the last two columns of my table so I sorted based on the second last and then the last: sortedData = sorted( activeOrderData, key=lambda row: (row[-2],row[-1]) ) ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 19, 2024, 7:35pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/16 "2024-06-19T19:35:14Z")
> [@pkhoshroo](#): > > Your solution worked better cause when you have a table with multiple pages, the expression binding does not work. No, you just have to use a binding on a custom property to do the sorting. Then provide that to the table dataset, that way you can do the sorting prior to it being "paged". ---
**Author:** ![pkhoshroo](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pkhoshroo/32/26801_2.png) [@pkhoshroo](https://forum.inductiveautomation.com/u/pkhoshroo)\ **Post date:** [June 19, 2024, 7:43pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/17 "2024-06-19T19:43:54Z")
I did expression binding on Data Property of the power table and it did not work, but maybe yes I could use your method to do binding on custom property. ---
**Author:** ![Francisco.Mejia](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/francisco.mejia/32/63246_2.png) [@Francisco.Mejia](https://forum.inductiveautomation.com/u/Francisco.Mejia)\ **Post date:** [June 19, 2024, 8:07pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/18 "2024-06-19T20:07:03Z")
Is there any reason why it needs to be sorted in front end and not the database itself? ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [June 19, 2024, 9:33pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/19 "2024-06-19T21:33:51Z")
She never said a database was involved. Let's make things clearer: If your data comes from a query and you can sort it there, then sure, it's the way to go. But if you're asking there how to sort a dataset, I assume it's because you can't do it in your queries. Also note that the solution I suggested is NOT on the front end. It will not be executed by the browser, but by the gateway. ---
**Author:** ![Francisco.Mejia](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/francisco.mejia/32/63246_2.png) [@Francisco.Mejia](https://forum.inductiveautomation.com/u/Francisco.Mejia)\ **Post date:** [June 20, 2024, 12:14pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/20 "2024-06-20T12:14:58Z")
In post below it is mentioned that the dataset is the result of a SQL query Is there a way to sort a dataset on two different columns. The \*\*\*\*data in the dataset is the result of \*\* **a SQL query.** Lets say i have a dateset with 10 column\*\*s. I have a column that has a flag that is set for example either UP or Down. Another column has a Sequence number that is unique for the two different types. So in the data I have 10 UPs and 10 Downs for a total of 20 rows. I want to sort first on the Type and get all my UPs, then within each type I want to sort by the sequence number so i end ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [June 20, 2024, 12:18pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/21 "2024-06-20T12:18:41Z")
But this post you mention is not from the same user. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [June 20, 2024, 12:24pm UTC](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105/22 "2024-06-20T12:24:52Z")
That is the post she linked and not her post. Just because she linked to a post which references a SQL query doesn’t mean that she is getting her data from a SQL query. To answer your question directly, if the user has the ability to change the sort order then for relatively small data sets it will be more performant to do the sort in the front end. As the dataset grows in size that will shift to the back end. There is really no reason here to suggest one way or the other, and since the OP was already doing the sort on the front end the suggested solution was also front end based. [Next page](https://forum.inductiveautomation.com/t/sort-dataset-based-on-two-column/92105.md?page=2)