Select Unique Rows Based on Single Distinct Column

There is a situation when we need to retrieve only unique row based on one column. And also used join two tables to retrieve unique data. In this case we use distinct keyword to select unique records.

Suppose we have a two tables named ProductDetails and wishlist.

Product Table

PId bigint
ProductCode varchar(200)
ProductName varchar(200)
ProductDescription varchar(MAX)

Here is data of Product details table. 

PId     ProductCode     ProductName    ProductDescription
43     NBP-202          NBP-202           NBP-202
44     NBP-203         NBP-203            NBP-202
45     NBP-204         NBP-204            NBP-204
46     NBP-205        NBP-205            NBP-205
47     NBP-206        NBP-206            NBP-206

---------------------------------------------------------------------
Wishlist
Id bigint
userid bigint
PId bigint
Create_At datetime

Here is data of wishlist table:

Id     userid    PId     Create_At
3       2         45       2014-04-16
4      4          47       2014-04-16
5      4          45      2014-04-16
6     4           45      2014-04-16
7     4          46      2014-04-16
---------------------------------------------------------------------------
Now we want to retrieve unique product id with the entire row and also count number of user who add the product to his wishlist.
Here is the query

select distinct w.PId,P.ProductCode,p.ProductName, (select COUNT(*) from wishlist l where l.PId = w.PId) totaluser from wishlist w join ProductDetails p on w.PId=p.PId

And Now here is the output of above query.

PId     ProductCode     ProductName    totaluser.
45          NBP-204       NBP-204          3
46          NBP-205       NBP-205          1
47          NBP-206       NBP-206          1

If u have any query regards this feel free to ask me.