Removing duplicate records in sql view

Question

I have a query that returns data from a table that has duplicate records for column 1, though there might be different values in other columns. I would like to only bring in one record per value from column 1 into a view using a criteria to choose the correct record.

Here is the query;

SELECT 
   PrimaryColumn1,
   Column2,
   Column3,
   Date1,
   Date2
FROM
   My_Table

I would like to have only distinct values in PrimaryColumn1 in the view I am creating based on the latest date in Date1, and if that is a duplicate as well, in Date2.

I tried doing the below but was not able to make it work

SELECT 
   PrimaryColumn1,
   Column2,
   Column3,
   Date1,
   Date2
FROM    
   (SELECT  
        [PrimaryColumn1,
        Column2,
        Column3,
        Date1,
        Date2,
        ROW_NUMBER() OVER(PARTITION BY [Date1] ORDER BY Date2) AS RowNumber
    FROM    
        My_Table)
WHERE   
    RowNumber = 1

Any help would be greatly appreciated.

After the suggestion below, the final query looked like this:

SELECT 
    PrimaryColumn1,
    Column2, 
    Column3,
    Date1,
    Date2
FROM    
    (SELECT  
         [PrimaryColumn1,
         Column2,
         Column3,
         Date1,
         Date2,
         ROW_NUMBER() OVER(PARTITION BY PrimaryColumn1
                           ORDER BY Date1 DESC, Date2 DESC) AS RowNumber) data
WHERE 
    RowNumber = 1

Answer 1

I think your ROW_NUMBER() statement should look like this:

ROW_NUMBER() OVER(PARTITION BY PrimaryColumn1
                      ORDER BY Date1 DESC, Date2 DESC) AS RowNumber

Since you're looking for the most recent record for each PrimaryColumn1 value, this should do what you want (as I understand it).

Answer 2

CROSS APPLY is a great way to do something like this. For example, this pulls a record for each CategoryID in the Products table and shows the product data for the most expensive product in each category.

This effectively gives you a way to use a correlated subquery in a join. Pretty cool.

USE Northwind;
GO
--Get a distinct list of CategoryID values
--From the dbo.Products table, and show the
--Product details for the most expensive product
--in each of those categories....
SELECT DISTINCT 
  Prod.CategoryID,
  TopProd.ProductID,
  TopProd.ProductName,
  TopProd.UnitPrice
FROM dbo.Products AS Prod
CROSS APPLY 
(
  --Find the top 1 product in each category by unitprice
  SELECT TOP 1 
    ProductID,
    ProductName,
    UnitPrice
  FROM dbo.Products
  --This is the "correlated" part where 
  --we filter by the outer queries' CategoryID
  WHERE CategoryID = Prod.CategoryID
  --The ORDER BY determines which product
  --you see for each category.  In this
  --case I'll get the most expensive product
  ORDER BY UnitPrice DESC
) AS TopProd;