# Reordering a query resultset by list of ids

**URL:** <https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [April 26, 2023, 7:28am UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906 "2023-04-26T07:28:48Z")\
**Posts on this page:** 14\
**Page:** 2

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2023, 1:18pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/21 "2023-04-26T13:18:59Z")

</div>

I think a lambda should be able to handle it. I think this part below in double asterisks just needs to be edited to match `row[i]` if index 1 of the order is not None, otherwise, match `[row[i][0], None]`... but I haven't ever spent the time to understand the lamdba function.. edit: although how they're used in that link makes sense

```auto
data.sort(key=lambda(row): tuple(**order.index(row[i])** for order, i in zip(order_lists, indices)))

```

---

<div class="post-metadata">

**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:** [April 26, 2023, 1:39pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/22 "2023-04-26T13:39:52Z")

</div>

Using a function would allow you to catch a `ValueError` from `.index()` if the value is not found in an order list, and set up a default value there.  
Or, make a wrapper for `.index()` and try/catch there.

```python
def find_index(l, v):
	try:
		return l.index(v)
	except ValueError:
		return None

def sortds(ds, fields, order_lists):
	headers = list(ds.getColumnNames())
	data = list(system.dataset.toPyDataSet(ds))
	indices = [headers.index(f) for f in fields]
	data.sort(key=lambda(row): tuple(find_index(order, row[i]) for order, i in zip(order_lists, indices)))
	return system.dataset.toDataSet(headers, data)

```

This gets rid of the `ValueError` you got earlier.  
I also believe the ordering should be fine.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2023, 1:42pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/23 "2023-04-26T13:42:19Z")

</div>

Yep, that did the trick! I have to learn more about lambda functions... I thought I `sort` of understood them, but apparently not... 😵‍💫  
Thanks for your help!

Edit: printing `l` and `v` in that function actually helps my understanding of lambda 🙂

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2023, 1:50pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/24 "2023-04-26T13:50:09Z")

</div>

Oops, found it doesn't work for this situation:

```auto
ds = system.dataset.builder(part_id=int, part_length=int).build()
ds = system.dataset.addRows(ds, [
	[1000, 2500],
	[1001, 2200],
	[1002, 254],
	[1003, 1250],
	[1, 3700],
	[0, 2200],
	[0, 2200],
	[1, 2250]])
pds = system.dataset.toPyDataSet(ds)
pds = sortds(ds, ['part_id', 'part_length'], [
	[1, 1, 0, 1000, 0, 1001, 1003, 1002], [3700, 2250, 2200, None, 2200, None, None, None]
	])

```

I added 0 after 1000 in the order list

it orders like this:

| Row | part\_id | part\_length |
| --- | --- | --- |
| 0 | 1 | 3700 |
| 1 | 1 | 2250 |
| 2 | 0 | 2200 |
| 3 | 0 | 2200 |
| 4 | 1000 | 2500 |
| 5 | 1001 | 2200 |
| 6 | 1003 | 1250 |
| 7 | 1002 | 254 |

instead of:

| Row | part\_id | part\_length |
| --- | --- | --- |
| 0 | 1 | 3700 |
| 1 | 1 | 2250 |
| 2 | 0 | 2200 |
| 3 | 1000 | 2500 |
| 4 | 0 | 2200 |
| 5 | 1001 | 2200 |
| 6 | 1003 | 1250 |
| 7 | 1002 | 254 |

---

<div class="post-metadata">

**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:** [April 26, 2023, 1:52pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/25 "2023-04-26T13:52:16Z")

</div>

Y'all are killing me. Make the DB do all the work:

```auto
SELECT id, others
FROM (
	SELECT table.id, pairs.idx, table.others
	FROM table INNER JOIN (
		SELECT ? as id, 0 as idx
		UNION ALL
		SELECT ?, 1
		UNION ALL
		SELECT ?, 2) pairs
	ON table.id = pairs.id
	UNION
	SELECT -3600, 100000000, others
	UNION
	SELECT -25300, 100000001, others
) subq
ORDER BY idx, ....

```

Instead of dynamically generating a simple list of IDs for an `IN` clause, construct a constant table of `id` and `idx` pairs. The `idx` part is simply sequential.

{ Edited to make it use `runPrepQuery` substitutions. }

---

<div class="post-metadata">

**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:** [April 26, 2023, 2:01pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/26 "2023-04-26T14:01:16Z")

</div>

Wait, what ?  
Why should it sort it like the second ?

What do you expect to happen when a value appears more than once in an order list ?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 26, 2023, 2:06pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/27 "2023-04-26T14:06:47Z")

</div>

The list of parts is the order a machine will use to create certain part ids. The operator can select part ids in any order and as many of the same part as they like. So the part\_ids list could end up with duplicate part\_ids throughout the list, non-sequential to each other, like in the "instead of" table above.

@pturmel Handing it off to the DB was definitely my hope! Thanks for the skeleton, now I just need to massage my much-larger-than-example query to suit 😵‍💫

> [@nminchin](#):
>
> 1st question: is it possible to order by the part Ids in the query itself?

However, I don't think quarter to midnight is the time! 🫠 This will be tomorrow-Nick's problem

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [April 26, 2023, 2:12pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/28 "2023-04-26T14:12:42Z")

</div>

Hard agree. The database is made for sorting data. Let it do it.

---

<div class="post-metadata">

**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:** [April 26, 2023, 2:16pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/29 "2023-04-26T14:16:14Z")

</div>

That changes everything. I didn't realize there were as many elements in the order lists as in the data, and that they were supposed to match.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 28, 2023, 12:04pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/30 "2023-04-28T12:04:46Z")

</div>

Well I got that query working, but it failed for my 9600 parts. I copied the entire SQL query into npp and it's 393KB. I pasted into pgAdmin and I get this 😬. Gonna try changing this, but anything else I should be doing?

```auto
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
SQL state: 54001

```

It's fairly long...

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/0/80f05d20866a6b39dcace6166f1f2e084732a95b.png)

---

<div class="post-metadata">

**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:** [April 28, 2023, 12:28pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/31 "2023-04-28T12:28:18Z")

</div>

> [@nminchin](#):
>
> but it failed for my 9600 parts

Oy! How many result rows are you expecting?

Consider a temporary table to hold the ordering pairs. (Or is the ordering already derived from something in the database?)

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 28, 2023, 12:29pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/32 "2023-04-28T12:29:46Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/b/6b0b0bc4075e396cc6e6e7a563354566de84add8.png)

wait what? I didn't know you could do this?

> [@pturmel](#):
>
> How many result rows are you expecting?

upwards of 9500 😬  
I may have to return it in stages, where the user will have to scroll up blocks

> [@pturmel](#):
>
> Consider a temporary table to hold the ordering pairs. (Or is the ordering already derived from something in the database?)

the order is derived by the operator, so not from a table anywhere. How would I create the temp table? and would it be better than the screenshot above from chatgpt?

---

<div class="post-metadata">

**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:** [April 28, 2023, 12:36pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/33 "2023-04-28T12:36:49Z")

</div>

Huh. A constant table made from a `VALUES` clause. That's new to me. I wonder how widespread support for that is. Try?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [April 28, 2023, 12:46pm UTC](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906/34 "2023-04-28T12:46:58Z")

</div>

it seems to work in pgadmin 🙂 i'm just having an issue where the perspective client isn't using the saved new script atm....

Also, do I get an award for teaching _you_ something??? (even if it was chatgpt that taught me 😄)

[Previous page](https://forum.inductiveautomation.com/t/reordering-a-query-resultset-by-list-of-ids/72906.md?page=1)