Get Dataset From DB

I need to get the top 5 items in a MS SQL database table (descending order) and looking for the best way to accomplish this in Ignition. I have a table that is collecting data for a production line and need to get the top 5 names and times for those stations. It is very likely to have less than 5 with greater than zero, so I will ignore zero values.

table:
index, int
station, nvarchar(50)
time, int

What would the best method of getting this data be, transaction group or gateway script, or something else?

Thanks!

It depends on what you want to do with this data.

Do you need the data in a client or gateway? What do you want to do with it?

You probably just need a database query in Python using system.db.runQuery.

result = system.db.runQuery("SELECT TOP 5 station,time from table ORDER BY time DESC")

You will need to have a database connection setup in Ignition and have the project using that connection as its default database connection.

This would be a gateway script. I have a table that is tracking current downtime and I need to show a chart with the top 5 downtime locations and the total time they were down, all that is included in a current status table.

If you need the data in a chart then you do not need the data in the gateway, you need it in a runtime client because charts are in clients. More specifically you need the data in a client window, in a chart.

Perhaps what you should do is write a database query for a SQL Query binding on the “DB Pens” property on an Easy Chart in an Ignition window. Or do the same thing on the “Data” property of a Chart or Bar Chart component.

I used a chart and did a query with selecting TOP into a chart

SELECT TOP(5) Station, ShiftDownTime
FROM tblStatCurState
WHERE (ShiftDownTime > 0)
ORDER BY ShiftDownTime DESC

I will modify this some more based on getting a specific area for the given screen which is assigned individually by location already.

Thanks for the help