SQLTeam.com | Weblogs | Forums

Trouble with one distinct value with several columns


#1

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

BTW I like this new design of the forum =)


#2

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;

#3

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.


#4

This is a Microsoft SQL Server forum. I don't know the behavior or syntax in MySQL. You might want to pose the question in a MySQL forum.


#5

Yeah youre right. I think this is a good forum and thought maybe some knew MySQL very well also