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
The answer may be SQL verstion dependent. Which are you using?
An answer from StackOverflow may help.
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
oh , the version is MSSQL , im a little lost here , would X be CRTM? , or would t?
OK, forget the earlier example. Try this.
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.
1 Like
it says MAX is not recognized as
a built in function?
Try the modified version. I forgot that you had renamed the columns using the AS function.
1 Like
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!
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.
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 
its looking like the data is all in the right spot, just the name of the one column is blank instead of create time
Give it a name then. Change
SELECT
MAX(F_Create_Time),
F_Name_Lot, ...
to
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.
2 Likes