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

If you are, in fact, wanting the "number of distinct products", the DISTINCT keyword won't help you much. Instead, you'll need to COUNT the records in Orders grouped by supplier and order date. — Zec
– Zec, Commented Feb 5, 2014 at 1:03

har07 · Accepted Answer · 2014-02-05 01:06:36Z

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 :

edited Feb 5, 2014 at 1:06

answered Feb 5, 2014 at 1:01

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

allendks45 Over a year ago

Great resource references. Was sort of led into believing DISTINCT was a requirement for this problem, however looking at the references the accuracy of the results and data are more important. More prudent to learn correct syntax and code that compiles and returns correct data.

BrettFromLA · Accepted Answer · 2014-02-05 00:54:40Z

1

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

answered Feb 5, 2014 at 0:54

BrettFromLA

9261 gold badge7 silver badges17 bronze badges

Collectives™ on Stack Overflow

Using DISTINCT in SQL Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related