Let us see if second time is the charm :) If the following does not work, can you post some representative sample data? In the code below, you have to specify some ordering criterion (see "SomeColumn" in the query) and it will pick the first row based on that ordering criterion.;WITH cte AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY COALESCE(pp1.ProjectName,pp2.ProjectName) ORDER BY SomeColumn) AS RN,
tbGRNHistory.SupplierAccNo,
tbGRNHistory.GRNInDate,
tbGRNHistory.PONumber,
tbOrderHistory.SONumber,
tbOrderHistory.AccountNo AS CustAccNo,
tbCustomers.CustomerName,
tbOrderHistory.SubAddressCode,
PP2.ProjectName,
tbGRNHistory.Value
FROM tbGRNHistory
LEFT JOIN tbPOrderHistory
ON tbGRNHistory.PONumber = tbPOrderHistory.PONumber
LEFT JOIN tbOrderHistory
ON tbPOrderHistory.SONumber = tbOrderHistory.SONumber
LEFT JOIN tbCustomers
ON tbOrderHistory.AccountNo = tbCustomers.AccountNumber
LEFT JOIN dbo.uvTemp_PP_Projects AS PP1
ON tbOrderHistory.AccountNo = PP1.AccountNo
LEFT JOIN dbo.uvTemp_PP_Projects AS PP2
ON tbOrderHistory.SubAddressCode = PP2.SubAddress
GROUP BY
tbGRNHistory.SupplierAccNo,
tbGRNHistory.GRNInDate,
tbGRNHistory.PONumber,
tbOrderHistory.SONumber,
tbOrderHistory.AccountNo,
tbCustomers.CustomerName,
tbOrderHistory.SubAddressCode,
tbGRNHistory.Value,
PP2.ProjectName
)
SELECT * FROM cte WHERE RN=1; 
Advanced joins
Posted - 10/03/2012 : 07:08:48
