Looking to filter out the duplicates in a query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Looking to filter out the duplicates in a query

Author

Topic

velnias2010
Posting Yak Master

125 Posts

Posted - 2009-11-11 : 04:40:22

Heres my query

SELECT                       tblProducts.ProductName, tblProducts.ProductID, tblProducts.SubmissionDate, tblProducts.Importer, tblProducts.ImporterAddress1, tblProducts.ImporterAddress2, 
                      tblProducts.ImporterAddress3, tblProducts.ImporterAddress4, tblProducts.ImporterCountry, ProductClassification.Name AS ProductClassification, Format.Name, 
                      TargetPopulation.Name AS TargetPopulation, tblProductMISClassification.statusName AS ProductCategory, tblProducts.MISClassificationID
FROM         tblProducts LEFT OUTER JOIN
                      TargetPopulation ON tblProducts.TargetPopulationID = TargetPopulation.TargetPopulationID LEFT OUTER JOIN
                      Format ON tblProducts.FormatID = Format.FormatID LEFT OUTER JOIN
                      ProductClassification ON tblProducts.ProductClassificationID = ProductClassification.ProductClassificationID LEFT OUTER JOIN
                      tblProductMISClassification ON tblProducts.MISClassificationID = tblProductMISClassification.id
WHERE     (tblProducts.SubmissionDate >= '20091111') AND (tblProducts.SubmissionDate <= '20091111')
ORDER BY tblProducts.SubmissionDate, tblProducts.ImporterAddress4

Now the problem there could be items with the same Product Name in there. If there is then I would like to only get the most recent one and not have the older one in my result set.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-11-11 : 07:15:03

You can use row_number function, try to include it ike this

row_number()OVER(partition by tblProducts.ProductName order by tblProducts.SubmissionDate desc)
This will give you row_number partitioned on your productname and ordered by SubmissionDate you can change these variables to your accordance and then use CTE or SubQuery method to get records here is the complete listing

SELECT * FROM(
SELECT tblProducts.ProductName, tblProducts.ProductID, tblProducts.SubmissionDate, tblProducts.Importer, tblProducts.ImporterAddress1, tblProducts.ImporterAddress2,
tblProducts.ImporterAddress3, tblProducts.ImporterAddress4, tblProducts.ImporterCountry, ProductClassification.Name AS ProductClassification, Format.Name,
TargetPopulation.Name AS TargetPopulation, tblProductMISClassification.statusName AS ProductCategory, tblProducts.MISClassificationID,
row_number()OVER(partition by tblProducts.ProductName order by tblProducts.SubmissionDate desc) as RN
FROM tblProducts LEFT OUTER JOIN
TargetPopulation ON tblProducts.TargetPopulationID = TargetPopulation.TargetPopulationID LEFT OUTER JOIN
Format ON tblProducts.FormatID = Format.FormatID LEFT OUTER JOIN
ProductClassification ON tblProducts.ProductClassificationID = ProductClassification.ProductClassificationID LEFT OUTER JOIN
tblProductMISClassification ON tblProducts.MISClassificationID = tblProductMISClassification.id
WHERE (tblProducts.SubmissionDate >= '20091111') AND (tblProducts.SubmissionDate <= '20091111')
ORDER BY tblProducts.SubmissionDate, tblProducts.ImporterAddress4)T
where t.rn=1
=============================

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources