SQL Query tag, need to pull information from one of two rows

Ignition
, ,
1

I've got a series of SQL Queries that need to pull information from a database that 2 different devices report to. They both create a new row at around the same time, and are not always consistent in which comes first. So the information I need could be in either the last or second to last row of the table.

I've got a script that pulls in the information in the databse query browser, but when I try and use it in a SQL Query tag it fails to work.

SELECT testnum FROM welltest_welltest
WHERE
testnum >= (SELECT MAX(testnum)-1 FROM welltest_welltest)
AND module_id = 'cc663ec0-28dc-4e06-8549-d3df132a1ddd' --SP 330

In this case the result should be an integer value, but I need to be able to repeat this for several other values like string, float, and datetime.

2

The preferred way to find rows with the min/max value in a column is to use ORDER and LIMIT like this:

SELECT testnum
FROM welltest_welltest
WHERE module_id = 'cc663ec0-28dc-4e06-8549-d3df132a1ddd'
ORDER BY testnum DESC LIMIT 2;
1 Like
3

Thanks, I'll keep that in mind next time I need to order things. The issue was actually that the SQL query tag's query type was set to automatic, while I needed to specify it as a select function. I think the fact that I had the second select function in the original code with the max function is what was throwing it off.

4

Actually, it occurred to me that my answer is MySQL/MariaDB specific. If you're using other database server types it might be different. (I'm just used to thinking in terms of those DBs all the time and forget others exist...)