# Removing Almost duplicate rows from MSSQL named query **URL:** \ **Category:** Ignition\ **Created:** [October 29, 2022, 4:47pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841 "2022-10-29T16:47:48Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![IgnitionNewbie](https://avatars.discourse-cdn.com/v4/letter/i/94ad74/32.png) [@IgnitionNewbie](https://forum.inductiveautomation.com/u/IgnitionNewbie)\ **Post date:** [October 29, 2022, 4:47pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/1 "2022-10-29T16:47:48Z")
```auto SELECT SGRP_EXT.F_CRTM AS F_Create_Time, PART_LOT.F_NAME AS F_Name_Lot, PART_DAT.F_NAME AS F_NAME_Part, PRCS_DAT.F_NAME AS F_NAME_Process, TEST_DAT.F_NAME AS F_NAME_Test, PART_DAT.F_PTGP AS F_Part_SubGroup, PART_LOT.F_HOLD FROM SGRP_EXT INNER JOIN PART_LOT ON SGRP_EXT.F_LOT = PART_LOT.F_LOT INNER JOIN PART_DAT ON PART_DAT.F_PART = PART_LOT.F_PART INNER JOIN PRCS_DAT ON SGRP_EXT.F_PRCS = PRCS_DAT.F_PRCS INNER JOIN TEST_DAT ON SGRP_EXT.F_TEST = TEST_DAT.F_TEST WHERE (SGRP_EXT.F_CRTM >= 1664661664 AND PART_DAT.F_PTGP = 1318407675 AND SGRP_EXT.F_PRCS = 1317287292 AND SGRP_EXT.F_TEST = 1317203737 AND PART_LOT.F_HOLD = 0 AND PART_LOT.F_CTEST = 0 AND PART_LOT.F_CCPNT = 0 AND PART_LOT.F_CLOSE = 0) AND NOT (SGRP_EXT.F_CRTM >= 1664661664 AND PART_DAT.F_PTGP = 1318407675 AND SGRP_EXT.F_PRCS = 1317293344 AND SGRP_EXT.F_TEST = 1317201556 AND PART_LOT.F_HOLD = 0 AND PART_LOT.F_CTEST = 0 AND PART_LOT.F_CCPNT = 0 AND PART_LOT.F_CLOSE = 0) ORDER BY PART_DAT.F_NAME, SGRP_EXT.F_LOT ``` So Ive been making this query that takes all this info I need from 1 part group and then subtracts all the same info from another part group from the first one , and thats all working fine and dandy but i need to figure out how to remove almost duplicate rows from the query( I cant permanently delete them either because im not allowed to write to this database) , whenever someone accidentally puts the same data in this database twice it makes 2 of the same rows of information , with the only difference being the SGRP\_EXT.F\_CRTM (create time) being different , and since the create time is different , its not considered a perfect duplicate row , so I cant use SELECT DISTINCT because it doesn't do anything to that row I have also tried to use a GROUP BY , but i can't seem to get it to work ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 29, 2022, 5:04pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/2 "2022-10-29T17:04:38Z")
The answer may be SQL verstion dependent. Which are you using? An answer from [StackOverflow](https://stackoverflow.com/questions/69760941/how-do-i-eliminate-duplicate-rows-where-only-timestamp-is-different) may help. ```auto WITH x AS ( SELECT *, row_number() OVER(PARTITION BY make, model ORDER BY Timestamp DESC) rn FROM t ) SELECT Make, Model, Timestamp, Id FROM x WHERE rn = 1 ``` ---
**Author:** ![IgnitionNewbie](https://avatars.discourse-cdn.com/v4/letter/i/94ad74/32.png) [@IgnitionNewbie](https://forum.inductiveautomation.com/u/IgnitionNewbie)\ **Post date:** [October 29, 2022, 5:59pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/3 "2022-10-29T17:59:37Z")
oh , the version is MSSQL , im a little lost here , would X be CRTM? , or would t? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 29, 2022, 7:29pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/4 "2022-10-29T19:29:02Z")
OK, forget the earlier example. Try this. ```auto SELECT MAX(F_Create_Time) AS F_Create_Time, F_Name_Lot, F_NAME_Part, F_NAME_Process, F_NAME_Test, F_Part_SubGroup, PART_LOT.F_HOLD FROM ( -- Original query here. You may need to remove comments for Ignition. SELECT SGRP_EXT.F_CRTM AS F_Create_Time, PART_LOT.F_NAME AS F_Name_Lot, PART_DAT.F_NAME AS F_NAME_Part, PRCS_DAT.F_NAME AS F_NAME_Process, TEST_DAT.F_NAME AS F_NAME_Test, PART_DAT.F_PTGP AS F_Part_SubGroup, PART_LOT.F_HOLD FROM SGRP_EXT INNER JOIN PART_LOT ON SGRP_EXT.F_LOT = PART_LOT.F_LOT INNER JOIN PART_DAT ON PART_DAT.F_PART = PART_LOT.F_PART INNER JOIN PRCS_DAT ON SGRP_EXT.F_PRCS = PRCS_DAT.F_PRCS INNER JOIN TEST_DAT ON SGRP_EXT.F_TEST = TEST_DAT.F_TEST WHERE (SGRP_EXT.F_CRTM >= 1664661664 AND PART_DAT.F_PTGP = 1318407675 AND SGRP_EXT.F_PRCS = 1317287292 AND SGRP_EXT.F_TEST = 1317203737 AND PART_LOT.F_HOLD = 0 AND PART_LOT.F_CTEST = 0 AND PART_LOT.F_CCPNT = 0 AND PART_LOT.F_CLOSE = 0) AND NOT (SGRP_EXT.F_CRTM >= 1664661664 AND PART_DAT.F_PTGP = 1318407675 AND SGRP_EXT.F_PRCS = 1317293344 AND SGRP_EXT.F_TEST = 1317201556 AND PART_LOT.F_HOLD = 0 AND PART_LOT.F_CTEST = 0 AND PART_LOT.F_CCPNT = 0 AND PART_LOT.F_CLOSE = 0) ORDER BY PART_DAT.F_NAME, SGRP_EXT.F_LOT ) t GROUP BY F_Name_Lot, F_NAME_Part, F_NAME_Process, F_NAME_Test, F_Part_SubGroup, PART_LOT.F_HOLD ``` How it works: It runs your original query - inside the FROM ( ... ) section. The outer query uses group functions on all the subquery fields except the F\_Create\_Time field which returns the MAX timestamp. Change this to MIN if you want. Let me know if that works for you. ---
**Author:** ![IgnitionNewbie](https://avatars.discourse-cdn.com/v4/letter/i/94ad74/32.png) [@IgnitionNewbie](https://forum.inductiveautomation.com/u/IgnitionNewbie)\ **Post date:** [October 29, 2022, 8:10pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/5 "2022-10-29T20:10:14Z")
it says MAX is not recognized as a built in function? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 29, 2022, 8:15pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/6 "2022-10-29T20:15:33Z")
Try the modified version. I forgot that you had renamed the columns using the AS function. ---
**Author:** ![IgnitionNewbie](https://avatars.discourse-cdn.com/v4/letter/i/94ad74/32.png) [@IgnitionNewbie](https://forum.inductiveautomation.com/u/IgnitionNewbie)\ **Post date:** [October 29, 2022, 8:48pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/7 "2022-10-29T20:48:29Z")
It didnt like the ORDER BY inside of it, but after I took that out its at least executing it looks like its in order still anyway? what sorcery is this 😮 looks like its working! ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 29, 2022, 9:02pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/8 "2022-10-29T21:02:53Z")
Hmmm, it's a bit tricky for me as I can't test. If the sort order isn't right then add the ORDER BY clause at the end and use the field names used in the GROUP BY clause. ---
**Author:** ![IgnitionNewbie](https://avatars.discourse-cdn.com/v4/letter/i/94ad74/32.png) [@IgnitionNewbie](https://forum.inductiveautomation.com/u/IgnitionNewbie)\ **Post date:** [October 29, 2022, 9:04pm UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/9 "2022-10-29T21:04:15Z")
im not sure if its even needed anymore because this is executing in order without it edit : oh i got order by to execute when it was put on the outside too , i think its good to go 🙂 ---
**Author:** ![IgnitionNewbie](https://avatars.discourse-cdn.com/v4/letter/i/94ad74/32.png) [@IgnitionNewbie](https://forum.inductiveautomation.com/u/IgnitionNewbie)\ **Post date:** [October 30, 2022, 3:30am UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/10 "2022-10-30T03:30:04Z")
its looking like the data is all in the right spot, just the name of the one column is blank instead of create time ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [October 30, 2022, 7:24am UTC](https://forum.inductiveautomation.com/t/removing-almost-duplicate-rows-from-mssql-named-query/65841/11 "2022-10-30T07:24:06Z")
Give it a name then. Change ```auto SELECT MAX(F_Create_Time), F_Name_Lot, ... ``` to ```auto SELECT MAX(F_Create_Time) AS F_Create_Time, F_Name_Lot, ... ``` That will give the new MAX column the same name as the old column it is derived from. I've updated the full query in post number 4.