# Sorting with lambda **URL:** \ **Category:** Ignition\ **Tags:** scripting\ **Created:** [October 19, 2023, 1:17pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692 "2023-10-19T13:17:10Z")\ **Posts on this page:** 16\ **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:** [October 19, 2023, 1:17pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/1 "2023-10-19T13:17:10Z")
Hello all, I'm struggling to figure out this simple problem after lots of research. I am attempting to sort a PyDataSet by ApprenticeGroup 0, then ApprenticeGroup descending, then hours descending, then seniority ascending. Here is my current code: ```auto returnedData = system.dataset.toPyDataSet(system.db.runNamedQuery("Overtime/Trade Tables/Mechanics Sat Sun", {"Date" :Date})) NewValue = sorted( dict(zip(returnedData.columnNames, list(row))) for row in returnedData, key = lambda columns: (1 if columns['ApprenticeGrp'] == 0 else 0, columns['ApprenticeGrp'], columns['SunHrs'], columns['Senority']), reverse = True) ``` When I put "-" in front of the columns['Senority'] to try and make it sort in ascending order, I am met with a TypeError as you can't do this to strings. How would I work around this? Is there a way? Thanks in advance for taking the time to read this post/help me out. ---
**Author:** ![kyler.kamyszek](https://avatars.discourse-cdn.com/v4/letter/k/a4c791/32.png) [@kyler.kamyszek](https://forum.inductiveautomation.com/u/kyler.kamyszek)\ **Post date:** [October 19, 2023, 1:20pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/2 "2023-10-19T13:20:45Z")
Can you just sort it on the SQL side in the name query? ---
**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:** [October 19, 2023, 1:24pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/3 "2023-10-19T13:24:39Z")
I can't, it's through a button with an interactive JSON table unfortunately. ---
**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:** [October 19, 2023, 1:28pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/4 "2023-10-19T13:28:38Z")
Sorry, I'm not following, your code is clearly calling a Named Query, why can't you just enforce the sort order there? ---
**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:** [October 19, 2023, 1:29pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/5 "2023-10-19T13:29:36Z")
It runs the named query but does not follow the sorting unless I invoke it this way. It's a dataset vs a JSON table. ---
**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:** [October 19, 2023, 1:33pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/6 "2023-10-19T13:33:21Z")
> [@Robert\_Hrynciw](#): > > It's a dataset vs a JSON table. I'm not sure that matters. What component is displaying this data? A table? Tables will happily accept a dataset as opposed to a JSON structure. Or more properly, assuming this is for a perspective table (you haven't tagged the post but JSON is a good hint), then you can provide the sort order in the columns prop. EDIT: A screen snip to show what I mean: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/c/1c63d5ec96fb2baa82d1269d4dd6be17e6979ab0.png) ---
**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:** [October 19, 2023, 1:37pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/7 "2023-10-19T13:37:57Z")
At some point, it's clearer and easier to just make a function and use that instead. But, to the point.. Why not switch 0 and 1, and remove the `reverse=True` ? edit: nevermind, didn't see you need a different order for the last column Also, if you get a `TypeError`... you can cast things. Can you give a sample input and expected output ? What do the `senority` values look like ? ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [October 19, 2023, 2:14pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/8 "2023-10-19T14:14:48Z")
Define ```auto def getSortOrderCode(senority): if senority == "A": return 1 elif senority == "B": return 2 elif senority == "C": return 3 elif senority == "M": return 4 elif senority == "P": return 5 ``` and change the sort to ```auto sortedData2 = sorted( dict(zip(returnedPyData.columnNames, list(row))) for row in returnedPyData, key = lambda columns: (1 if columns['ApprenticeGrp'] == 0 else 0, columns['ApprenticeGrp'], columns['SunHrs'],getSortOrderCode(columns['Senority']) ), reverse = True) ``` ---
**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:** [October 19, 2023, 2:44pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/9 "2023-10-19T14:44:25Z")
> [@Tim\_Carlton](#): > > ```auto > def getSortOrderCode(senority): > if senority == "A": > return 1 > elif senority == "B": > return 2 > elif senority == "C": > return 3 > elif senority == "M": > return 4 > elif senority == "P": > return 5 > > > ``` ```python return "ABCMP".index(senority) ``` But this only work for very simple cases though, where `senority` is only ever one letter and in those defined. Which is why I'd really like a sample input. ---
**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:** [October 19, 2023, 2:50pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/10 "2023-10-19T14:50:19Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/4/d4b69a790c33a673bee51eadbb5b737dc237888d.png) These dropdowns are the reason I have to do them the way I do. These dropdowns can be edited from within the JSON table. If I use a regular system.db.runNamedQuery, I cannot have these dropdowns within the table. Sorry for not explaining better, I had a meeting and was in a rush. 😕 ---
**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:** [October 19, 2023, 2:53pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/11 "2023-10-19T14:53:05Z")
So, for example this is the problem I'm currently trying to sort around: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/d/8df048d12733dc1175ca9f2f6dd1d229a8c16da5.png) These 3 operators all have the same amount of hours (576). After hours, they are chosen by seniority for who will get approved/denied. With this 3-way tie, Willey should get approved first, then Hade, then Malcolm. I can sort in descending, but not ascending so that's where I run into the problem I'm facing now. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [October 19, 2023, 2:53pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/12 "2023-10-19T14:53:36Z")
Sure, but I am guessing that Seniority is a small enumerated list (apprentice, journeyman, master, etc.), and that list will be known and stable and managed here. I'd put the list in the database and return SortOrder in the query, or several other options. If senority is YearsOfService that has been stored as a string then maybe cast the string as a number and multiply by -1. More info would lead us to a more optimal solution. ---
**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:** [October 19, 2023, 3:03pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/13 "2023-10-19T15:03:21Z")
Seniority is a datetime from when they started, ApprenticeGroup is what you are thinking about with 0 being not an apprentice, 5 being bid/placed, 4 and down as years of apprentice. So Seniority is after they are already sorted by who is/isn't an apprentice if that makes sense. ---
**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\ **Post date:** [October 19, 2023, 3:22pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/14 "2023-10-19T15:22:18Z")
If it is a date (or a string containing a date) then return total milliseconds of the date (or -1 \* total\_millis). Take into account any business rules if there can be gaps in service that count or don't count. ---
**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:** [October 19, 2023, 3:24pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/15 "2023-10-19T15:24:56Z")
try this: ```python now = system.date.toMillis(system.date.now()) data = sorted( system.dataset.toPyDataSet(ds), key=lambda (name, group, hours, seniority): ( group == 0, group, hours, now - system.date.toMillis(seniority) ), reverse = True ) ``` initial dataset: | name | group | hours | senority | | --- | --- | --- | --- | | steve | 0 | 488 | 07-06-0099 | | jim | 5 | 476 | 10-09-2000 | | ron | 4 | 472 | 10-16-2000 | | mark | 0 | 409 | 09-08-1998 | | mike | 3 | 407 | 06-28-2010 | | stacy | 3 | 402 | 03-20-1995 | | brent | 0 | 401 | 10-30-1995 | | chuck | 5 | 401 | 01-06-2023 | | howard | 4 | 395 | 06-28-2010 | resulting dataset: | name | group | hours | senority | | --- | --- | --- | --- | | steve | 0 | 488 | 07-06-0099 | | mark | 0 | 409 | 09-08-1998 | | brent | 0 | 401 | 10-30-1995 | | jim | 5 | 476 | 10-09-2000 | | chuck | 5 | 401 | 01-06-2023 | | ron | 4 | 472 | 10-16-2000 | | howard | 4 | 395 | 06-28-2010 | | mike | 3 | 407 | 06-28-2010 | | stacy | 3 | 402 | 03-20-1995 | adjust things to match the order you need, but it should get you started. Also, you might not need `now` at all, simply putting a minus sign should be enough edit: yes, steve's been there for 2000 years. ---
**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:** [October 19, 2023, 4:38pm UTC](https://forum.inductiveautomation.com/t/sorting-with-lambda/80692/16 "2023-10-19T16:38:13Z")
Steve is a trooper! We need more people like Steve. This did the trick, thank you very much!