Using DISTINCT to find the unique values of one column while displaying multiple colums

Question

I want to use the SELECT DISTINCT statement to filter the results of my result table. I'm trying to display the names of the products (ProductName) that have unique discount percentages (DiscountPercent). That way, it only shows products that have unique discount percentages.

My code is as follows:

select    
    p1.ProductName, p2.DiscountPercent
from
    Products p1
right join
    (select distinct DiscountPercent 
     from Products) p2 on p1.DiscountPercent = p2.DiscountPercent
order by 
    ProductName --I need to have it ordered by ProductName

This returns a table; however, the table still has duplicate information in the DiscountPercent column. That's what I'm trying to get rid of.

Please keep in mind that I am new to SQL Server and coding in general. Any help would be greatly appreciated!

(I have searched for an answer to this multiple times, implementing solutions from other questions; however, their solutions always displayed my result table with only the ProductName column.)

Try 'SELECT PRODUCTNAME, DISTINCT DISCOUNTPERCENT FROM PRODUCTS ;' — Gopi, Mar 17 '19 at 03:42
@Gopi I get an error by the DISTINCT word, saying "Incorrect syntax near 'DISTINCT'". — ptud2, Mar 17 '19 at 04:10
My bad , use 'select distinct discountpercent, productname from products' — Gopi, Mar 17 '19 at 04:29
@Gopi I get a result table; however, there are still rows that show duplicate percentages. The product names, on the other hand, are all unique. I'm trying to get all unique percentages regardless if all of the productnames are on the result table. — ptud2, Mar 17 '19 at 04:35
You say "distinct" but it seems fairly obvious that you expect a product to have multiple discount values. So what do you REALLY mean by "distinct"? Connor has given you a method, but you need to think about your goal. In a situation where "Product X" has 2 or more discount values, what do you expect in your results? Lastly, it is always important to post DDL for your tables so that others can understand the relationships between them (if any). And if you do search and find/attempt solutions posted by others, include the link. — SMor, Mar 17 '19 at 12:30

score 1 · Accepted Answer · answered Mar 17 '19 at 04:59

Distinct checks for totally unique records, i.e. it's going to check the ProductName and DiscountPercent against the other records and will filter out results that match both values. Your JOIN won't work because it will match records from one table to as many of the other table it can.

Here is a solution based on a similar question:

;WITH a AS (
    SELECT ProductName, DiscountPercent,
           ROW_NUMBER() OVER (PARTITION BY DiscountPercent ORDER BY ProductName ASC) AS RowNumber
     FROM Products
)
SELECT a.ProductName, a.DiscountPercent
 FROM a
 WHERE a.RowNumber = 1

Basically, ROW_NUMBER() OVER (PARTITION BY DiscountPercent ORDER BY ProductName ASC) AS RowNumber counts repeat values in DiscountPercent. WHERE a.RowNumber = 1 tells the query to get the first one found.

Using DISTINCT to find the unique values of one column while displaying multiple colums

1 Answers1