DISTINCT function is not working in my SQL query-CodePudding

I'm a beginner in SQL and am working on this project. It's asking me to pull a distinct list of clients (ClientID) that have ever made an purchase, sorted by ClientID ascending.

I seem to have everything right except for the fact that my results aren't distinct, I am still getting duplicates in the ClientID. What could I do to fix this? Thanks in advance! Here is my query:

SELECT DISTINCT c.ClientID, ClientName, o.PurchaseID   

FROM Purchases o JOIN Clients c   

ON o.ClientID = c.ClientID 

ORDER BY c.ClientID ASC;

CodePudding user response：

The reason is the following code:

SELECT DISTINCT c.CustomerID, CustomerName, o.OrderID

This means you select distinct combination of CustomerID, CustomerName and OrderID. Due to you need dicticnt CustomerID, you can write like bellowing:

SELECT DISTINCT c.CustomerID, c.CustomerName

FROM Orders o JOIN Customers c   

ON o.CustomerID = c.CustomerID 

ORDER BY c.CustomerID ASC

CodePudding user response：

Since you need the distinct list of customers who has made an order, you should be able to select it from the Orders tables itself.

SELECT DISTINCT CustomerID 
FROM Orders order by CustomerID

should be able to fetch the unique list of CustomerID's