Using DISTINCT in a select query

[Lorimare]

My question is this:

Is there any way you use DISTINCT in a select query involving multiple fields and not get dupes? For example Im trying to run the following:

SELECT DISTINCT tbl_OH.phone_num, tbl_OH.customer_id, tbl_OH.last_name, tbl_OH.first_name, tbl_OH.zip5, tbl_OH.Current, tbl_OH.order_date
FROM tbl_OD INNER JOIN tbl_OH ON tbl_OD.order_num = tbl_OH.order_num
GROUP BY tbl_OH.phone_num, tbl_OH.customer_id, tbl_OH.last_name, tbl_OH.first_name, tbl_OH.zip5, tbl_OH.Current, tbl_OH.order_date
HAVING (((tbl_OH.Current) Not In ("CMPL","DEL"," &quot) AND ((tbl_OH.order_date)=#1/21/2004#-4));


I DO NOT want duplicate phone numbers returned but I am getting dupes. The reason is...is that if one value is different in a row it's not treated as a dupe.

So...is there any way to return DISTINCT phone numbers in a multi field query? If I do a select Distinct tbl_OH.phone_number from tbl_OH I WILL get NO DUPES. But if i start adding other fields in the query I WILL get dupes.

Any help?

 

[hneal98]

Can you show an example of what you want and what you are getting?

 

[Lorimare]

Thank you.
A example would be phone number: 9135772110. I will do a query on my main query to check for dupes and will find the above phone number as one of the dupes.

If I copy that phone number and paste it in a search(Find) in my main query I will find the phone number and its dupe but if read down the row customer_number or current or one ofo the other fields' value is different thus meeting the DISTINCT requirement.

I want to have a query with multiple fields that will return EACH distinct phone # with ZERO dupes.

This is kind of hard to explain so let me know if you need further data.

 

[Lorimare]

Ok here, is a better example. Lets say I'm researching a duplicate phone number of:1111111111 Upon seaching my main query for the above phone number I find there are two numbers of 4078913807 with the rest of the fields as follows:

phone_num customer_id last_name first_name
1111111111 031022222 SMITH BOB

1111111111 031033333 SMITH KATE


Even though the query was a DISTINCT I still got the duplicate phone number of 1111111111 and the reason as you can see above is becuase the customer_id value is different for each row as well as the first_name values. I want to alter my query so I get zero duplicates on phone_num field regardless if any of the other fields in the row are different or not.

 

[Lorimare]

Ok here, is a better example. Lets say I'm researching a duplicate phone number of:1111111111 Upon seaching my main query for the above phone number I find there are two numbers of 1111111111 with the rest of the fields as follows:

phone_num customer_id last_name first_name
1111111111 031022222 SMITH BOB

1111111111 031033333 SMITH KATE


Even though the query was a DISTINCT I still got the duplicate phone number of 1111111111 and the reason as you can see above is becuase the customer_id value is different for each row as well as the first_name values. I want to alter my query so I get zero duplicates on phone_num field regardless if any of the other fields in the row are different or not.

 

[Golom]

Sorta of a rock and a hard place. Suppose you had

Phone    CustomerID    LastName    FirstName ...

5551234     123           Smith        Joe
5551234     456           Jones        Mary

If you are returning all fields but want only ONE occurence of a phone number ... which of these records would you expect to appear? And how do you decide?

 

[Golom]

You could do something like

SELECT tbl_OH.phone_num, 
       FIRST(tbl_OH.customer_id) As CustomerID, 
       FIRST(tbl_OH.last_name) As LastName, 
       FIRST(tbl_OH.first_name) As FirstName, 
       FIRST(tbl_OH.zip5) As ZipCode, 
       FIRST(tbl_OH.Current) As CurrentVal, 
       FIRST(tbl_OH.order_date) As OrderDate

FROM tbl_OD INNER JOIN tbl_OH ON tbl_OD.order_num = tbl_OH.order_num

GROUP BY tbl_OH.phone_num

HAVING tbl_OH.Current Not In ("CMPL","DEL"," ")) 
       AND tbl_OH.order_date =#1/21/2004#-4

 

[tracy1234]

jonair - if you are really just looking for distinct phone numbers, then there is absolutely no reason to include the rest of your fields in your select statement.

If you are looking for distinct records, then your query is functioning as expected.

-Tracy

 

[hneal98]

If you are looking for just one phone number, but you want one of the names, you can use a max or min function, or first or last function to take the first, or max, or min, or last record and group by the phone number.

The whole thing doesn't make much sense though as mentioned above.