# SQL query for a table datasets's top 10 defect value

**URL:** <https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563>\
**Category:** Ignition\
**Created:** [January 19, 2015, 8:05am UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563 "2015-01-19T08:05:23Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Don\_t\_shoot](https://avatars.discourse-cdn.com/v4/letter/d/db5fbb/32.png) [@Don\_t\_shoot](https://forum.inductiveautomation.com/u/Don_t_shoot)\
**Post date:** [January 19, 2015, 8:05am UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/1 "2015-01-19T08:05:23Z")

</div>

I want to make a report which need a dataset to always show top 10 defect quality in ‘defect\_log’ database table , and the report’s form like the picture in attachment , and I want to write the Sql query binding in the table datasets of a analysis controller , how can I write the sql to get the table data source ? (the key point is “always top 10 defect quality to show”) Thanks a lot .  
 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/1/1b70607399c1688395e0408c33ef90149ab17da2.png)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 19, 2015, 7:46pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/2 "2015-01-19T19:46:11Z")

</div>

Doing a ‘top N within a group’ is not is simple as it sounds, especially if the data is in a tall format. Things get wonky quickly and can be a pain in the backside to deal with.

My opinion is to use a script to grab, sort, and write the data to the dataset for the report.

If you can supply with a sample of data, then we can work on a further solution for you. 🙂

Also: What platform are you using (MySQL, SQL Server, etc)?

---

<div class="post-metadata">

**Author:** ![Don\_t\_shoot](https://avatars.discourse-cdn.com/v4/letter/d/db5fbb/32.png) [@Don\_t\_shoot](https://forum.inductiveautomation.com/u/Don_t_shoot)\
**Post date:** [January 20, 2015, 2:55am UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/3 "2015-01-20T02:55:10Z")

</div>

Thanks for the help Jordan, I use MYsql . And the attachment is a simple table data, for your reference , I think this file can help . Don’t care about the column ‘yield’ ,you can just make a data source table ongly have “machine”,“line”,“date”,“defect 1 quantity” ,“defect 2 quantity”,“defect 3 quantity”…“defect 5 quantity”, Thanks a lot .  
[Simple table data.csv](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/dc49ca23ae739559a79ef14b417a25f1006504b2.csv) (106 Bytes)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 20, 2015, 1:35pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/4 "2015-01-20T13:35:46Z")

</div>

Just took look at the file you posted. There’s no defect data here. 😮

Is there anything in the defect\_log data? If so, make an export of that table (csv or sql is fine) and post it here. 😉

---

<div class="post-metadata">

**Author:** ![Don\_t\_shoot](https://avatars.discourse-cdn.com/v4/letter/d/db5fbb/32.png) [@Don\_t\_shoot](https://forum.inductiveautomation.com/u/Don_t_shoot)\
**Post date:** [January 20, 2015, 3:14pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/5 "2015-01-20T15:14:56Z")

</div>

Sorry ,I think I forgot save ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:")  
[Simple defect\_log table.csv](https://us1.discourse-cdn.com/inductiveautomation/original/2X/7/70a1e9b122430fec9667a823db845c0492ac2139.csv) (3.36 KB)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 20, 2015, 6:51pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/6 "2015-01-20T18:51:39Z")

</div>

That looks better! 😃

Okay, just so I’m on the same page:

1. What are the relationships between the three tables?
2. In the defect\_log, what is the runID column?
3. In the defect\_log, what is the quantity column?
4. What is the hierarchy (priority) of sorting? Currently I’m thinking:  
[ul]a) date  
b) line  
c) machine  
d) defect types and quantities in descending order[/ul]
5. are the quantities summed up, or are we just counting the number of occurrences (example: MMS happened 4 times on 2014-12-06)? Or both (MMS occurred 4 times for a total of 279)

---

<div class="post-metadata">

**Author:** ![Don\_t\_shoot](https://avatars.discourse-cdn.com/v4/letter/d/db5fbb/32.png) [@Don\_t\_shoot](https://forum.inductiveautomation.com/u/Don_t_shoot)\
**Post date:** [January 21, 2015, 2:19am UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/7 "2015-01-21T02:19:35Z")

</div>

Thanks for the answer Jordan.

1.the relationship between these three tables as below:  
Machine table.machineID=Line table.parent ID  
Line table.Line Name=defect\_log table .line

2.Don’t care about the ‘runID’ in defect\_log table

3.The priority you thought is OK.

4.The quantities summed up ,I think it’s OK.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 23, 2015, 12:19pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/8 "2015-01-23T12:19:10Z")

</div>

Sorry, finally able to get back to this.

I imported you data into a db table, so that a query can do the first round of sorting:

`select date_format(entryTimestamp,'%Y-%m-%d') as t_stamp, line, productCode, defectCode, sum(quantity) as qty
from test.defect_log
group by t_stamp, line, productCode, defectCode
order by t_stamp, line, productCode, qty desc`  
gives a result set like this:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/3c659bc3d776c2e1edcc174b5bf2ecacfa9b3352.png)  
  
Now, we can use a script to format a dataset:

[code]query=""“SELECT date\_format(entryTimestamp,’%Y-%m-%d’) as t\_stamp, line, productCode, defectCode, sum(quantity) as qty  
FROM test.defect\_log  
GROUP BY t\_stamp, line, productCode, defectCode  
ORDER BY t\_stamp, line, productCode, qty desc”""

dataIn=system.db.runQuery(query)

lastDate=’’  
lastLine=’’  
lastProd=’’

headers=[“Date”, “line”, “productCode”, “defect1”, “defect2”, “defect3”, “defect4”, “defect5”, “defect6”, “defect7”, “defect8”, “defect9”, “defect10”]  
newData=  
addRowFlag=0  
for row in dataIn:  
if row[‘t\_stamp’] != lastDate or row[‘line’] != lastLine or row[‘productCode’] != lastProd:  
if addRowFlag == 1:  
newData.append(newRow)  
newRow=[row[‘t\_stamp’], row[‘line’], row[‘productCode’], str(row[‘defectCode’]) + ': ’ +str(int(row[‘qty’])), ‘-’, ‘-’, ‘-’, ‘-’, ‘-’, ‘-’, ‘-’, ‘-’, ‘-’]  
defectIndex = 4  
addRowFlag = 1  
lastDate = row[‘t\_stamp’]  
lastLine= row[‘line’]  
lastProd = row[‘productCode’]  
else:  
if defectIndex \<= 12:  
newRow[defectIndex] = str(row[‘defectCode’]) + ': ’ +str(int(row[‘qty’]))  
defectIndex += 1

event.source.parent.getComponent(‘Table’).data=system.dataset.toDataSet(headers, newData)

[/code]

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4dfaf6a233f0cb419c577314ced8ba8cf66e9e1c.png)  

I have some things to take care of this morning, but I will edit/add to this post to document the script… especially since it’s not documented at all… 😆

---

<div class="post-metadata">

**Author:** ![Don\_t\_shoot](https://avatars.discourse-cdn.com/v4/letter/d/db5fbb/32.png) [@Don\_t\_shoot](https://forum.inductiveautomation.com/u/Don_t_shoot)\
**Post date:** [January 25, 2015, 8:24am UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/9 "2015-01-25T08:24:06Z")

</div>

Thank you so much Jordan, I do some alter to the script for my real project , no error ,but no data in the DATA PROPERTY of ”Table“component , is there something wrong as below？Thanks again. 😃

query=""“SELECT A.Name as Area,L.Name as Line ,W.WORKORDER AS WORKORDER, W.ProductCode AS PartNumber,P.Description AS Description,S.FinishDateTime AS Date,T.tooling1 AS ToolNumber ,line.productioncount AS TotalOutputQuantity,(100\*{Root Container.RunQty}/(SUM(D.quantity)+{Root Container.RunQty})) AS Yield,sum(100 \* (D.quantity/{Root Container.RunQty})) AS DefectPercentage  
FROM ign\_runtime.area A INNER JOIN ign\_runtime.line L INNER JOIN ign\_runtime.schedule S INNER JOIN ign\_runtime.workorder W INNER JOIN ign\_runtime.productcode P INNER JOIN ign\_mes.tooling T INNER JOIN ign\_runtime.linepropertyhistory LINE INNER JOIN ign\_mes.defect\_log D ON W.ProductCode=P.ProductCode and S.WorkOrderID=W.ID and A.ID=L.ParentID and L.ID=S.LineID and T.workOrder=W.WorkOrder and Line.productcode=W.productcode and D.productcode=W.productcode”""

dataIn=system.db.runQuery(query)

headers=[“PartNum”,“Description”,“Machine”,“TotalOutputQty”,“YieldRate”,“Date”, “defect1”, “defect2”, “defect3”, “defect4”, “defect5”]  
newData=[]  
addRowFlag=0  
for row in dataIn:  
if row[‘Date’] != lastDate or row[‘Line’] != lastLine or row[‘PartNumber’] != lastProd:  
if addRowFlag == 1:  
newData.append(newRow)  
newRow[defectIndex]=[row[‘PartNumber’], row[‘Description’], row[‘Machine’], row[‘TotalOutputQuantity’], row[‘YieldRate’], str(row[‘DefectPercentage’]), ‘-’, ‘-’, ‘-’, ‘-’]  
7\<=defectIndex \<=10  
addRowFlag = 1  
defectIndex += 1

event.source.parent.getComponent(‘Table’).data=system.dataset.toDataSet(headers, newData)

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 26, 2015, 12:27pm UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/10 "2015-01-26T12:27:51Z")

</div>

Well, at first look, I’d say it’s because you’re missing some things I had in there. 😉 As my brother used to say about a go-cart we had as kids, “We kept fixing it until it didn’t work any more…” 😆

This should get you closer:

[code]query=""“SELECT A.Name as Area,L.Name as Line ,W.WORKORDER AS WORKORDER, W.ProductCode AS PartNumber,P.Description AS Description,S.FinishDateTime AS Date,T.tooling1 AS ToolNumber ,line.productioncount AS TotalOutputQuantity,(100\*{Root Container.RunQty}/(SUM(D.quantity)+{Root Container.RunQty})) AS Yield,sum(100 \* (D.quantity/{Root Container.RunQty})) AS DefectPercentage  
FROM ign\_runtime.area A INNER JOIN ign\_runtime.line L INNER JOIN ign\_runtime.schedule S INNER JOIN ign\_runtime.workorder W INNER JOIN ign\_runtime.productcode P INNER JOIN ign\_mes.tooling T INNER JOIN ign\_runtime.linepropertyhistory LINE INNER JOIN ign\_mes.defect\_log D ON W.ProductCode=P.ProductCode and S.WorkOrderID=W.ID and A.ID=L.ParentID and L.ID=S.LineID and T.workOrder=W.WorkOrder and Line.productcode=W.productcode and D.productcode=W.productcode”""

dataIn=system.db.runQuery(query)

# Initialize variables for comparing current Date, Line, and Product

lastDate=’’  
lastLine=’’  
lastPartNum=’’

# Initialize headers and data lists for our processed data

headers=[“PartNum”,“Description”,“Machine”,“TotalOutputQty”,“YieldRate”,“Date”, “defect1”, “defect2”, “defect3”, “defect4”, “defect5”]  
newData=[]

# This flag is so that we ignore adding a new row to newData at the very beginning

# (at start, lastDate, lastLine, and lastPartNum are always different.)

addRowFlag=0

for row in dataIn:  
if row[‘Date’] != lastDate or row[‘Line’] != lastLine or row[‘PartNumber’] != lastPartNum: #Look for a change  
if addRowFlag == 1:  
newData.append(newRow) # add row data to newData  
# initialize the data for the next row of data coming in  
newRow[]=[row[‘PartNumber’], row[‘Description’], row[‘Machine’], row[‘TotalOutputQuantity’], row[‘YieldRate’], row[‘Date’], str(row[‘DefectPercentage’]), ‘-’, ‘-’, ‘-’, ‘-’]  
defectIndex=7 # this points to where defect2 will go (defect1 was already put in when we started the new row)  
addRowFlag = 1 # After this point, any differences in PartNum, Date, or Line will result in a new row of data.  
lastDate = row[‘Date’] # Set comparison data  
lastLine= row[‘Line’]  
lastPartNum = row[‘PartNumber’]  
else:  
if defectIndex \<= 10: # As long as we’re not at the end of the row…  
newRow[defectIndex] = str(row[‘DefectPercentage’])  
defectIndex += 1  
event.source.parent.getComponent(‘Table’).data=system.dataset.toDataSet(headers, newData)  
[/code]

All that being said, there is no sorting going on in your query. If you look ay my example query:

`select date_format(entryTimestamp,'%Y-%m-%d') as t_stamp, line, productCode, defectCode, sum(quantity) as qty
from test.defect_log
group by t_stamp, line, productCode, defectCode
order by t_stamp, line, productCode, qty desc`  
The GROUP BY and ORDER BY Clauses are where the magic happens. The GROUP BY separates them out by Date, Line and defectCode. The ORDER BY sorts them into the order we want. You’ll want to do something similar to get the script working correctly.

One more thing I notice is that no defect codes are showing up in the query. If you are going with a ‘Top N’ sorting, it will be helpful tou your users if they can see what the defects are.

Take a closer look at the example I posted and see if you can tell how I added the defect codes to the end result.

---

<div class="post-metadata">

**Author:** ![Don\_t\_shoot](https://avatars.discourse-cdn.com/v4/letter/d/db5fbb/32.png) [@Don\_t\_shoot](https://forum.inductiveautomation.com/u/Don_t_shoot)\
**Post date:** [January 27, 2015, 12:51am UTC](https://forum.inductiveautomation.com/t/sql-query-for-a-table-datasetss-top-10-defect-value/9563/11 "2015-01-27T00:51:59Z")

</div>

Thanks Jordan, as a rookie ,I am your loyal fans now ![:prayer:](https://us1.discourse-cdn.com/inductiveautomation/original/1X/e5a7e3b63facc151ea73e3b45db7bdb3d4bdc316.gif ":prayer:") .