# Duplicated data

**URL:** <https://forum.inductiveautomation.com/t/duplicated-data/16094>\
**Category:** Ignition\
**Created:** [November 22, 2017, 2:57pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094 "2017-11-22T14:57:41Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![santiago4ever72](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@santiago4ever72](https://forum.inductiveautomation.com/u/santiago4ever72)\
**Post date:** [November 22, 2017, 2:57pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/1 "2017-11-22T14:57:42Z")

</div>

Hi everyone 🙂 Hoper everyone is doing ok. I currently assigned an SQL query to a table component in order to get data into the table from a database, I want to display the table in my dashboard but it currently duplicates the data I have and gives various results with slight changes. I only want to display results that have only the same nValue which corresponds to the status of a machine,  
The following is the query I’m currently using:

SELECT TOP 10 nValue, nCount,  
CONVERT(varchar, tsTotalDuration / 86400 ) + ‘:’ + – Days  
CONVERT(varchar, DATEADD(ms, ( tsTotalDuration % 86400 ) \* 1000, 0), 108) as “Duration(DD:HH:MM:SS)”,  
nShiftNumber, nKey, tLastModified  
FROM tblCPDataMessageProtocol  
WHERE nShiftNumber = {Can/Filler/Messages/Current Shift Number} AND nObjectNameLink = 1698  
ORDER BY nKey DESC, tLastModified DESC, nValue DESC;

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [November 22, 2017, 4:18pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/2 "2017-11-22T16:18:04Z")

</div>

Sounds like you’re looking for the [`GROUP BY`](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql) function.

---

<div class="post-metadata">

**Author:** ![santiago4ever72](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@santiago4ever72](https://forum.inductiveautomation.com/u/santiago4ever72)\
**Post date:** [November 23, 2017, 2:41pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/3 "2017-11-23T14:41:06Z")

</div>

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

This is what I’m getting as a result and I only want the last updated result from the database to be reflected on the table. Which would be the total duration of a machines Status/Program with its quantities (how much times that program/status was called on during the operating time of the machine. I understand group by would remove duplicates but as an error on my part these are not duplicates due to the fact that the rows are not the same. Is it possible to get the last updated record from the db with only one of each status or program values?

---

<div class="post-metadata">

**Author:** ![santiago4ever72](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@santiago4ever72](https://forum.inductiveautomation.com/u/santiago4ever72)\
**Post date:** [November 23, 2017, 3:42pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/4 "2017-11-23T15:42:08Z")

</div>

![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/0/0373315f3223cf7b2da9bbe801dff760a3a56e57.png)

I used this and the problem was solved (I think). Can you give me any feedback as to what you think of the query itself?

Hope this helps anyone with the same problem.

---

<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:** [November 23, 2017, 4:30pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/5 "2017-11-23T16:30:43Z")

</div>

Your query won’t return the latest result, just the largest. And the generated row can have the largest count from one record and largest duration from a different record. Consider using sum instead of max to get the true values for the time period, or constructing a join using a latest t\_stamp value (if you have one) to help you get the true latest count and duration.

---

<div class="post-metadata">

**Author:** ![santiago4ever72](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@santiago4ever72](https://forum.inductiveautomation.com/u/santiago4ever72)\
**Post date:** [November 30, 2017, 3:26pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/6 "2017-11-30T15:26:35Z")

</div>

Max did in fact just give me the largest value and not what I was expecting. Values were totally wrong. Tried using sum but did not work either. It jut added every record which gave me a total duration of about a month per shift. Tried checking the time stamp but I have no latest time stamp currently for the records in the db or dont understand how to use it in a query 😢

---

<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:** [November 30, 2017, 4:10pm UTC](https://forum.inductiveautomation.com/t/duplicated-data/16094/7 "2017-11-30T16:10:09Z")

</div>

> [@santiago4ever72](#):
>
> but I have no latest time stamp currently for the records in the db

Every row in the DB needs a timestamp.