Im having trouble finding distinct numbers in a specified column. I have several columns and DISTINCE command works on all of the columns as I understand it. Here is my code.
SELECT BOP AS OP,
B.LAST_CHANGE AS LAST_CHANGE,
B.WORK_FIELD AS WORK_FIELD,
left (B.WORK_DEP,3) AS DEPARTMENT,
B.REASON AS REASON,
B.SCRAP AS SCRAP,
B.SCRAP_COS AS SCRAP_COS,
B.DELETED AS DELETED,
A.MATNR AS MATNR,
A.VK AS VK,
B.ORDER_NR AS ORDER_NR,
C.MRP_CONT AS MRP_CONT,
C.PRO_TYPE AS PRO_TYPE,
left(B.REASON,1) AS AVD,
right(B.REASON,1) AS TYP
from ADW.DB3_HEAD A
inner join ADW.SCRAP_HEAD B ON A.ORDER_NR = B.ORDER_NR
inner join ADW.ARTSTATDAY C ON C.MATNR = A.MATNR AND C.PLANT = A.PLANT
where left(B.WORK_FIELD,2) IN ('55','56') and B.DELETED <> 'X' and B.LAST_CHANGE >= '2008-01-01'
What I want is the distinct values of the column DEPARTMENT
If there is more than one row for a given DEPARTMENT, which of those rows do you want to get? If you don't care, then here is one way. If you do care about which of those rows you pick up, change the ORDER BY clause in the ROW_NUMBER function to make that row the first.
;WITH cte AS
(
SELECT BOP AS OP ,
B.LAST_CHANGE AS LAST_CHANGE ,
B.WORK_FIELD AS WORK_FIELD ,
LEFT(B.WORK_DEP, 3) AS DEPARTMENT ,
B.REASON AS REASON ,
B.SCRAP AS SCRAP ,
B.SCRAP_COS AS SCRAP_COS ,
B.DELETED AS DELETED ,
A.MATNR AS MATNR ,
A.VK AS VK ,
B.ORDER_NR AS ORDER_NR ,
C.MRP_CONT AS MRP_CONT ,
C.PRO_TYPE AS PRO_TYPE ,
LEFT(B.REASON, 1) AS AVD ,
RIGHT(B.REASON, 1) AS TYP,
ROW_NUMBER() OVER (PARTITION BY DEPARTMENT ORDER BY (SELECT NULL)) AS RN
FROM ADW.DB3_HEAD A
INNER JOIN ADW.SCRAP_HEAD B ON A.ORDER_NR = B.ORDER_NR
INNER JOIN ADW.ARTSTATDAY C ON C.MATNR = A.MATNR
AND C.PLANT = A.PLANT
WHERE LEFT(B.WORK_FIELD, 2) IN ( '55', '56' )
AND B.DELETED <> 'X'
AND B.LAST_CHANGE >= '2008-01-01'
)
SELECT * FROM cte WHERE RN = 1;
Thanks!This ROW_NUMBER seems very interesting. However I get the error message You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cte AS
If I remove the ;WITH cte AS
I Insted get the error message FUNCTION ROW_NUMBER does not exist
Maybe Im doing something wrong on my end. I will look into it and see if I can find a solution.