Distinct not working in Sql Server

Once I was trying to get distinct record from database, i found that my query was not working as of my expectation.
My table was like below
ProductId   ProductName   CreatedDate

----------   --------------   --------------
A001        Sample Data      2012-03-25 23:31:26.580
A001        Sample Data      2012-04-01 14:11:09.483

A002        Sample Data      2012-04-14 15:51:30.640

And the query which I wrote to fetch distinct record was as below

SELECT DISTINCT ProductId, ProductName, CreatedDate FROM tbl_Product

Reason behind the improper output was "DISTINCT removes redundant duplicate rows", to know more about DISTINCT click here

If you see my table you can find difference on CreatedDate of A001 record. Which means that row is not the duplicate.

In order to achieve proper output in such a case, use any of the below queries

SELECT P.ProductId, P.ProductName, MAX(P.CreatedDate) AS CreatedDate
FROM tbl_Product P
GROUP BY P.ProductId

OR

SELECT P1.ProductId, P1.ProductName, P1.CreatedDate
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY P.ProductId ORDER BY P.ProductId) AS RowNo,
P.ProductId, P.ProductName, P.CreatedDate
    FROM tbl_Product P
) P1
WHERE P1.RowNo = 1 

Posted in: SQL