# Sorting Using Python Sorted Function **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective, scripting\ **Created:** [May 25, 2023, 3:59pm UTC](https://forum.inductiveautomation.com/t/sorting-using-python-sorted-function/74181 "2023-05-25T15:59:08Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [May 25, 2023, 3:59pm UTC](https://forum.inductiveautomation.com/t/sorting-using-python-sorted-function/74181/1 "2023-05-25T15:59:09Z")
Hello, I recently created a JSON table with dropdowns inside of it. It is sorted certain ways with a SQL binding on page launch. When using a system.db.runNamedQuery script to return values based on different filters, I have to finish with this script in order to return it as a JSON object for the table: ```auto returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery(query, params)) NewDict = {'%s' % i: {colName: value for colName, value in zip(returnedData.columnNames, list(row))} for i, row in enumerate(returnedData)} self.getSibling("Table").props.data = NewDict.values() ``` I need to figure out how I can sort a table by a certain column in ascending or descending order (extra credit for a sort by another column after the first sort). So, for example this table is sorted by Hours ascending through a SQL binding returned as JSON.. ![Annotation 2023-05-25 115406](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/c/0c5cac0d8ddb4b434ec0ad043371780223d74130.png) If I am to select a craft of "Electricians" in my dropdown filter, I get this return... ![Annotation 2023-05-25 115626](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/5/3522af0f0bb27d93772fac63b2accfb4db1512e9.png) As you can see, it's sorting by Clock Number ascending. The script for this is as follows with just "sorted" in front of NewDict.values()... ```auto NewDict = {'%s' % i: {colName: value for colName, value in zip(returnedData.columnNames, list(row))} for i, row in enumerate(returnedData)} self.getSibling("Table").props.data = sorted(NewDict.values()) ``` Thank you in advance for your help. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [May 25, 2023, 4:21pm UTC](https://forum.inductiveautomation.com/t/sorting-using-python-sorted-function/74181/2 "2023-05-25T16:21:50Z")
`sorted` accepts a keyword argument `key`: [https://docs.python.org/2.7/library/functions.html#sorted](https://docs.python.org/2.7/library/functions.html#sorted) `key` should be a callable that returns a sortable item per value. Typically this is a simple lambda. Simplifying your script a little, and running it on some test data, you can return a sorted list with something like this: ```python returnedData = system.dataset.toPyDataSet(system.dataset.toDataSet( ["Clock Number", "Name", "Department", "Hours"], [ ["100003683", "Bob Vance", 9, 24.0], ["100008031", "Jim Hopper", 9, 32.6], ["100012345", "Amos Burton", 1, 40], ] )) return sorted( dict(zip(returnedData.columnNames, list(row))) for row in returnedData, key = lambda columns: columns['Hours'] ) ``` The lambda is a function that will be called for each item in the comparison. It accepts the single argument in the list (in this case, the dictionary per row); the lambda is declaring this as being named `columns`. Then `columns['Hours']` is returning the value of the 'Hours' column, which will be used for the sorting operation. If you wanted to sort by multiple values, you would return a tuple with each value to sort by, in order, e.g.: `key = lambda columns: (columns['Department'], columns['Hours'])` Where the return value will first be sorted by department, then by hours. ---
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [May 30, 2023, 12:16pm UTC](https://forum.inductiveautomation.com/t/sorting-using-python-sorted-function/74181/3 "2023-05-30T12:16:25Z")
Thank you very much! Saved me a lot of confusion in your explanation. I had seen docs but couldn't quite figure out what I was doing wrong with the key. I have one table where I sort a special way with the binding query... ```auto SELECT Lname, Fname, EID, Shift, ApprenticeGrp, Dept, CurrentHours, FirstShift as [Midnights], SecondShift as [Days], ThirdShift as [Afternoons], Status, ApprovedShift, ApprovedSecondShift FROM [Maintenance].[dbo].[MOS_Signup] WHERE TradeSkill = 0 and DateTime = :Date ORDER BY CASE WHEN ApprenticeGrp = 0 THEN ApprenticeGrp END desc, ApprenticeGrp desc, CurrentHours desc ``` Where I order by ApprenticeGrp 0 at the top, then ApprenticeGrp descending (5-1) then by CurrentHours descending. Is this possible? I know you can put them in reverse order (not sure how to do it with multiple column sorting, though) but I don't know if you can case as well. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [May 30, 2023, 3:27pm UTC](https://forum.inductiveautomation.com/t/sorting-using-python-sorted-function/74181/4 "2023-05-30T15:27:10Z")
Probably something like: ```python key = lambda columns: (1 if column['ApprenticeGroup'] == 0 else 0, column['ApprenticeGroup'], column['CurrentHours']), reverse = True, ``` You might also be able to use something like `not bool(column['ApprenticeGroup'])`, if you wanted to get fancy. But basically, for each row in your data you want to return a three-item tuple: | Value | Key 1 | Key 2 | Key 3 | | --- | --- | --- | --- | | {ApprenticeGroup: 0, CurrentHours: 20} | 1 | 0 | 20 | | {ApprenticeGroup: 0, CurrentHours: 10} | 1 | 0 | 10 | | {ApprenticeGroup: 5, CurrentHours: 10} | 0 | 5 | 10 | | {ApprenticeGroup: 4, CurrentHours: 10} | 0 | 4 | 10 | | {ApprenticeGroup: 1, CurrentHours: 10} | 0 | 1 | 10 | Which should sort the way you want. Remember that you can always use a simple negation if something isn't sorting the way you want; e.g. `-columns['ApprenticeGrp']` would give you the opposite result for a particular column in an otherwise ascending or descending sort. ---
**Author:** ![Robert\_Hrynciw](https://avatars.discourse-cdn.com/v4/letter/r/3bc359/32.png) [@Robert\_Hrynciw](https://forum.inductiveautomation.com/u/Robert_Hrynciw)\ **Post date:** [May 31, 2023, 2:06pm UTC](https://forum.inductiveautomation.com/t/sorting-using-python-sorted-function/74181/5 "2023-05-31T14:06:28Z")
Thank you very much, this solved my question! You're a lifesaver.