Using DISTINCT in SQL Query

Question

How do i use DISTINCT command in a SQL query to show the supplier id, company name, and the number of distinct products from that supplier that were ordered prior to a specific date? I ran the code in Access but it doesn't translate over to SQL efficiently. The table appears.

[Supplier ID    Company Name    Product Name    Order Date
1           Exotic Liquids  Chang           17-Aug-94
1           Exotic Liquids  Chang           22-Nov-94
1           Exotic Liquids  Aniseed Syrup   26-Sep-94]

The code I have so far is the following. Where I get confused is where to put the DISTINCT statement. Should it be immediately after the Select? Should it go in Parentheses in addition to the SELECT? Excuse my lack of knowledge on this subject in advance.

SELECT       Suppliers.SupplierID, Customers.CompanyName, Products.ProductName,    
             Orders.OrderDate

FROM         Suppliers INNER JOIN
             Products ON Suppliers.SupplierID = Products.SupplierID CROSS JOIN
             Customers INNER JOIN
             Orders ON Customers.CustomerID = Orders.CustomerID

WHERE Orders.OrderDate <='1/1/1999'
ORDER BY     Suppliers.SupplierID

Answer 1

You can either distinct by all columns selected :

SELECT DISTINCT 
    Suppliers.SupplierID, Customers.CompanyName, Products.ProductName,    
    Orders.OrderDate
FROM         
    Suppliers INNER JOIN
    Products ON Suppliers.SupplierID = Products.SupplierID CROSS JOIN
    Customers INNER JOIN
    Orders ON Customers.CustomerID = Orders.CustomerID
WHERE 
    Orders.OrderDate <='1/1/1999'
ORDER BY     
    Suppliers.SupplierID

or use group by instead if you need to distinct only by SupplierID. DISTINCT is not a function, hence DISTINCT(Suppliers.SupplierID) means the same as simply put DISTINCT word after SELECT in this case (see the 2nd reference below).

For Reference :

• http://blog.sqlauthority.com/2007/12/20/sql-server-distinct-keyword-usage-and-common-discussion/
• http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx

Answer 2

I'm pretty sure it's this:

SELECT       DISTINCT(Suppliers.SupplierID), Customers.CompanyName, Products.ProductName,Orders.OrderDate

FROM         Suppliers INNER JOIN
             Products ON Suppliers.SupplierID = Products.SupplierID CROSS JOIN
             Customers INNER JOIN
             Orders ON Customers.CustomerID = Orders.CustomerID

WHERE Orders.OrderDate <='1/1/1999'
ORDER BY     Suppliers.SupplierID