SELECT
	company,
	dept,
	customer,
	amount,
	person
FROM
	(	
		SELECT
			company,
			dept,
			customer,
			amount,
			person,
			ROW_NUMBER() OVER 
				(
					PARTITION BY company, dept, customer, amount, person 
					ORDER BY person
				) AS RowNum
		FROM
			MyTable
	) AS T
WHERE
	RowNum = 1

SELECT 
	dept, 
	person, 
	company, 
	customer,
	amount
FROM 
	(
		select 
			bc.dept, 
			person, bd.company AS company, 
			customer, 
			amount, 
			ROW_NUMBER() OVER(PARTITION BY dept, person, bd.company, customer, amount ORDER BY person) AS RowNum
		FROM
			table1 AS bc 
		INNER JOIN
			table2 AS bd 
			ON bc.company = bd.company 
			AND bc.BATCH = bd.BATCH 
		LEFT OUTER JOIN
			person_data 
			ON dept = person_data.company_ID COLLATE SQL_Latin1_General_CP1_CS_AS 
			AND bc.company = person_data.company 
		LEFT OUTER JOIN
			amount AS ad 
			ON bd.amtrsn = ad.amtcode 
			AND bd.company = ad.company
		WHERE 
			(bc.last_date between CONVERT(DATETIME, '2010-08-22 00:00:00', 102) and CONVERT(DATETIME, '2010-08-31 00:00:00', 102)) 
			AND (bd.flag1 = 'A') 
			AND (bd.flag2 = 'Y') 
			and (dept = 'shoes') 
			and (bd.company = 'co2')
	) AS T
WHERE
	RowNum = 1 

quote:

---

Originally posted by seanh1016

any idea how to get rid of duplicates where I sort of define a duplicate as row 2 below because all entries are the same except person?

DISTINCT seems to think this is a unique value, which, I guess it is; but I'd like to consider it a duplicate.

example: I'd like to remove row 2 here:
row 1: company1 dept1 customer1 amount1 person1
row 2: company1 dept1 customer1 amount1 person2

---