# NamedQuery Parameter Sorting

**URL:** <https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542>\
**Category:** Ignition\
**Tags:** namedqueries\
**Created:** [July 18, 2023, 7:09pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542 "2023-07-18T19:09:45Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Joe\_Bonhage](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/joe_bonhage/32/45543_2.png) [@Joe\_Bonhage](https://forum.inductiveautomation.com/u/Joe_Bonhage)\
**Post date:** [July 18, 2023, 7:09pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/1 "2023-07-18T19:09:45Z")

</div>

Hi all,  
Looking for some help with sort of parameters passed in a named query. When I pass the below params, the response back is out of order. (returns 3, 4, 1, 2) -- when a try the sorted() function on the params to get the order i need, it comes back properly ordered, but looks like it's in java notation, which then the namequery throws error because if it. Any thoughts to best method here to pass in parameters in a certain order and get back the values in that order?

#tagCal values are set by tag reads in a few lines above  
params = {"parCal1":tagCal1, "parCal2":tagCal2,"parCal3":tagCal3,"parCal4":tagCal4}  
sortedDict = sorted(params.items())  
print params  
print sortedDict  
query2 = system.db.runNamedQuery("nq\_jconn3\_mettrack\_prod" , params)

parCal1 = str(query2.getValueAt(0,0))  
DueDate1\_java = str(query2.getValueAt(0,7))

parCal2 = str(query2.getValueAt(1,0))  
DueDate2\_java = str(query2.getValueAt(1,7))

parCal3 = str(query2.getValueAt(2,0))  
DueDate3\_java = str(query2.getValueAt(2,7))

parCal4 = str(query2.getValueAt(3,0))  
DueDate4\_java = str(query2.getValueAt(3,7))

 ![results from para query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/a/da5b328cf83fdef0174a69e79b1f148ed073dd87.png)

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [July 18, 2023, 7:14pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/2 "2023-07-18T19:14:31Z")

</div>

Why does the order of the params matter?

---

<div class="post-metadata">

**Author:** ![Joe\_Bonhage](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/joe_bonhage/32/45543_2.png) [@Joe\_Bonhage](https://forum.inductiveautomation.com/u/Joe_Bonhage)\
**Post date:** [July 18, 2023, 7:21pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/3 "2023-07-18T19:21:27Z")

</div>

Thank Kevin. When I pass in a certain order, I"m using the below getValueAt(x,y)... so zero would be reply back to the first parameter passed. What is confusing me now is (0,0) is returning the values from parameter 3. I was expecting the values from parameter 1.

parCal1 = str(query2.getValueAt(0,0))  
DueDate1\_java = str(query2.getValueAt(0,7))

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [July 18, 2023, 7:22pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/4 "2023-07-18T19:22:04Z")

</div>

The order of the parameters you pass does not effect the results you get.

What does this named query look like?

---

<div class="post-metadata">

**Author:** ![Joe\_Bonhage](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/joe_bonhage/32/45543_2.png) [@Joe\_Bonhage](https://forum.inductiveautomation.com/u/Joe_Bonhage)\
**Post date:** [July 18, 2023, 7:22pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/5 "2023-07-18T19:22:55Z")

</div>

![named query](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5d0072af2e697977f58603e8c2f71fcdfc28d98a.png)

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [July 18, 2023, 7:23pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/6 "2023-07-18T19:23:32Z")

</div>

Ok, you see how these parameter values are just pulled out by name, and the actual order is part of the query structure?

---

<div class="post-metadata">

**Author:** ![Joe\_Bonhage](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/joe_bonhage/32/45543_2.png) [@Joe\_Bonhage](https://forum.inductiveautomation.com/u/Joe_Bonhage)\
**Post date:** [July 18, 2023, 7:24pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/7 "2023-07-18T19:24:58Z")

</div>

yeah I think I know where you are going here. The ORDER BY is controlling how the data retrieved.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [July 18, 2023, 7:40pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/8 "2023-07-18T19:40:53Z")

</div>

Not just that, but the query you've written only guarantees you get 4 rows back, and that each row's `I4201` column value matches _one of_ your input parameters.

It does not guarantee that you get _1 row per input parameter_, and certainly not in any order.

---

<div class="post-metadata">

**Author:** ![Joe\_Bonhage](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/joe_bonhage/32/45543_2.png) [@Joe\_Bonhage](https://forum.inductiveautomation.com/u/Joe_Bonhage)\
**Post date:** [July 18, 2023, 8:01pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/9 "2023-07-18T20:01:55Z")

</div>

Thanks.. yeah I'm re-writing the query.  
What I believe I want is a group by function, returning the latest calibration date, grouping by the ID.

---

<div class="post-metadata">

**Author:** ![Joe\_Bonhage](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/joe_bonhage/32/45543_2.png) [@Joe\_Bonhage](https://forum.inductiveautomation.com/u/Joe_Bonhage)\
**Post date:** [August 21, 2023, 9:13pm UTC](https://forum.inductiveautomation.com/t/namedquery-parameter-sorting/76542/10 "2023-08-21T21:13:54Z")

</div>

FYI -- cleaned up the query and python scripting to which the order of the parameters doesn't matter. I set up a dictionary input (key, param\_in) and dictionary output (key, query\_out) --- join the results based on the key and parse the tuple array from the result:

```auto
Query (aggregate results):
SELECT iCalID, max(cDateDue) as DueDate, max(iDescription) as xDescription -- max(iManufacturer) as Manufacturer, max(iModel) as Model,
FROM (
    SELECT I4201 as iCalID, I4202 as iManufacturer, I4203 as iModel, I4204 as iDescription, I4225 as iCalStatus,  
    DATEFORMAT(C2301, 'YYYY-MM-DD') as cDateLastCal, C2323 as cCalPass, DATEFORMAT(C2303, 'YYYY-MM-DD') as cDateDue
    FROM "mt"."Inventory" INNER JOIN "mt"."Calibration" ON "mt"."Inventory".MTAG = "mt"."Calibration".MTAG 
    WHERE "mt"."Inventory"."I4201" = :param1 or "mt"."Inventory"."I4201" = :param2
            or "mt"."Inventory"."I4201" = :param3 or "mt"."Inventory"."I4201" = :param4
    ) as results
    GROUP BY iCalID

```

Python scripting:

```auto

tagCal1 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/AFT/Station10/Cal-ID"])[0].value)
tagCal2 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/APS/Cal-ID"])[0].value)
tagCal3 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/LaserWelder/Cal-ID"])[0].value)
tagCal4 = str(system.tag.readBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/UltraSonicWelder/Cal-ID"])[0].value)

#pass values from tags into a dictionary for named query call
params = {"param1":tagCal1, "param2":tagCal2,"param3":tagCal3,"param4":tagCal4}
#print params

#Call the named query (found in project browser, named queries)
pyData = system.db.runNamedQuery("nq_jconn3_mettrack_prod" , params)
   
#brute force to get the results from the query
result_Cal1 = str(pyData.getValueAt(0,0))
result_Due1 = str(pyData.getValueAt(0,1))
result_Cal2 = str(pyData.getValueAt(1,0))
result_Due2 = str(pyData.getValueAt(1,1))
result_Cal3 = str(pyData.getValueAt(2,0))
result_Due3 = str(pyData.getValueAt(2,1))
result_Cal4 = str(pyData.getValueAt(3,0))
result_Due4 = str(pyData.getValueAt(3,1))

#define input and output dictionaries. We will key on CalID for final results
dict_input_swap = {v: k for k, v in params.items()} # swap key value pair
dict_output = {result_Cal1:result_Due1, result_Cal2:result_Due2, result_Cal3:result_Due3, result_Cal4:result_Due4}
#combine input and output dictionaries, key on CalID since it is common for both
from collections import defaultdict
dd = defaultdict(list)
for d in (dict_input_swap, dict_output):
    for key, value in d.items():
        dd[key].append(value)
final = dict(dd)

#Parse through a dictionary of tuples
#key = CalID, tuple[0] = parameter, tuple[1] = due date
for key in dd.keys():
	param = dd[key][0]
	calID = key
	expiryDate = dd[key][1]
	if param == "param1":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/AFT/Station10/Cal-Expiry"],[expiryDate])
	if param == "param2":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/APS/Cal-Expiry"],[expiryDate])
	if param == "param3":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/LaserWelder/Cal-Expiry"],[expiryDate])
	if param == "param4":
		system.tag.writeBlocking(["[MES_Tags]NorthAmerica/Boulder/Ligasure/BiZact/UltraSonicWelder/Cal-Expiry"],[expiryDate])

```