returning distinct values for database field

[aonefun]

How do I set this query to return only distinct values for DocumentHeaders.SoldToFax? (I thought this is accomplished by the GROUP BY function).

SELECT DocumentHeaders.SoldToFax, Min(DocumentHeaders.SoldToCompany) AS MSoldToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax

 

[r937]

you are absolutely correct, that query will produce one row per SoldToFax

the groups in a GROUP BY query are by definition distinct

r937.com | rudy.ca

 

[lespaul]

maybe try DISTINCT:

SELECT DISTINCT  DocumentHeaders.SoldToFax, Min(DocumentHeaders.SoldToCompany) AS MSoldToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[aonefun]

Thanks for your responseS. Here is the full code that still returns duplicate DocumentHeaders.SoldToFax or Document.ShipToFax values:


(SELECT DISTINCT DocumentHeaders.SoldToFax, Min(DocumentHeaders.SoldToCompany) AS MSoldToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.SoldToFax
HAVING (((DocumentHeaders.SoldToFax)<>""))
UNION
SELECT DISTINCT DocumentHeaders.ShipToFax, Min(DocumentHeaders.ShipToCompany) AS MShipToCompany, Min(DocumentHeaders.DataSource) AS MinOfDataSource, Min(DocumentHeaders.Remove) AS MinOfRemove, Min(DocumentHeaders.catagoryID) AS MinOfcatagoryID
FROM DocumentHeaders
GROUP BY DocumentHeaders.ShipToFax
HAVING (((DocumentHeaders.ShipToFax)<>"")))
UNION (SELECT faxnumbers.SoldToFax, faxnumbers.MSoldToCompany, faxnumbers.DataSource, faxnumbers.Remove, faxnumbers.CatagoryID
FROM faxnumbers);

 

[r937]

aonefun, if you show query 1 and get an answer, please do not expect the answer to apply also to query 2 which is substantially different

also, please don't keep creating new threads for essentially the same problem


leslie, the groups in a GROUP BY query are distinct by definition

there's no need to add DISTINCT

r937.com | rudy.ca

 

[lespaul]

I thought that having all those mins and grouping by the number should only return a single record for each one, but figured, what the hay!

Glad to know that I was right in my original thinking!!

les

 

[PWise]

if all that you want is a distinct list of all fax numbers
from soldtofax and shiptofax all that you need is

SELECT SoldToFax As fax
FROM DocumentHeaders
WHERE SoldToFax <> ''
union 
SELECT ShipToFax
FROM DocumentHeaders
WHERE ShipToFax <> '' 
UNION 
SELECT SoldToFax FROM faxnumbers;

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.