Sql-server – If Duplicate Only Return Oldest Entry

sql serversql-server-2008-r2t-sql

I need a way to return all data – and if there is a duplication in the data return the oldest entry. I tried to get creative and use the Row_Number() function, but when I run the query it only returns all the data where the database has multiple rows.
What I am after is returning all rows, and IF, the StoreNumber exists multiple times only return the oldest based off of OpenDate how should this query be modified so that not just the oldest is returned, but also the rest of the data as well?

;With CTE As
(
    Select
    StoreNumber = clsStoreUQ
    ,StoreName = StoreName
    ,RN = ROW_NUMBER() OVER (PARTITION BY StoreName ORDER BY OpenDate)
    ,MCC = ROUND(CAST(COALESCE(MCC,0) AS INT),0)
    ,SCC = ROUND(CAST(COALESCE(SCC,0) AS INT),0)
    ,OpenDate = RTRIM(LTRIM(OpenDate))
    FROM OPENQUERY(XX.XX.XX.XXX,'Select
                                    iv.clsStoreUQ
                                    ,cm.storeName StoreName
                                    ,iv.jbOpenDate OpenDate
                                    ,SUM(case when wp.SaleCat = 1 THEN CAST(jc.amount AS DOUBLE PRECISION) else 0 end) MCC
                                    ,SUM(case when wp.SaleCat = 3 THEN CAST(jc.amount AS DOUBLE PRECISION) else 0 end) SCC
                                    FROM saleData iv
                                    INNER JOIN StoreData cm
                                    ON cm.storeID = jb.storeID
                                    INNER JOIN saleDataSub jc
                                    ON jc.clsStoreUQ = jb.clsStoreUQ
                                    INNER JOIN saleCategory wp
                                    ON wp.SaleCat = ac.SaleCat
                                    GROUP BY iv.clsStoreUQ, cm.storeName,iv.jbOpenDate')
)
Select
* FROM CTE
WHERE RN = 1;

Best Answer

If you want only the oldest rows you should order them starting with the oldest.

i.e.

 RN = ROW_NUMBER() OVER (PARTITION BY StoreName ORDER BY OpenDate DESC)

But it's really strange that "there is added whitespace at the end of the dates", maybe you store the dates as strings and order by can have no sense of order in this case.