sql distinct 1

[aonefun]

While DISTINCT will bring results of only one record per SoldToCMAcountNo from the first query listed below, it has no effect from the second query as there are more than one field specified in the SELECT statement.

>How Do I alter the second query so that there is only one record per SoldToCMAcountNo. resulted in the query?

"SELECT DISTINCT SoldToCMAccountNo FROM etc.

"SELECT DISTINCT SoldToCMAccountNo, ID, DocStatus FROM etc.

 

[gmmastros]

Distinct works on ALL columns returned in the query. If you are getting duplicate SoldToCMAccountNo, then it must be the combinations of ID & DocStatus that is causing dups to be returned.

So, in this case, which record do you want?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aonefun]

I only want one record for every SoldToCMAcountNo, even when there are duplicates caused due to the inclusion of the other fields.

 

[gmmastros]

>> I only want one record for every SoldToCMAcountNo, even when there are duplicates caused due to the inclusion of the other fields.

I understood that from your original post. I can help you out with this too.

The thing is... since there are multiple records returned, it's because the data is different for each record. So, again, which record do you want? The one with the Max(ID), Min(ID), etc....

To successfully create this query, you would be better served to use a derived table that uses a min or max aggregate. Ex:

Select TableName.SoldToCMAcountNo, 
       TableName.Id,
       TableName.docStatus
From   TableName
       Inner Join (
         Select SoldToCMAcountNo, Min(Id) As MinId
         From   TableName
         Group By SoldToCMAcountNo
         ) as SomeAlias
         On TableName.SoldToCMAcountNo = SomeAlias.SoldToCMAcountNo
         And TableName.Id = SoldToCMAcountNo.MinId

Of course, this does not handle the case where there could be duplicate docstatus's per SoldToCMAcountNo/Id combinations.

My point, though... Notice the inner query is returning the Min(ID) for each SoldToCMAcountNo. This could be wrong. Perhaps you want the Max(Id). I don't know.

Does any of this make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aonefun]

the record id does not matter. As long as there is only one result per SoldToCMAccountNo.

Please go to:

http://www.canchair.com/customer_locator/customer_locator_postcode.asp

Type in the Postal Code "76555" and click Submit

You will see 3 identical results. This is the problem I face.

I sincerely appreciate your assistance!

 

[gmmastros]

>> the record id does not matter.

I was afraid you were going to say that. Every time I've ever heard that, it was because the database was not properly normalized. Of course, this makes me really sad because a denormalized database is usually slower than it needs to be, and the data it contains has a higher probability of being corrupt.

Since you have such low dis-regard for your data, I would recommend this query.

SELECT SoldToCMAccountNo, Min(ID) As ID, Min(DocStatus) As DocStatus
FROM   TableName
Group By SoldToCMAccountNo

I guarantee that you will only get 1 record per SoldToCMAccountNo. Of course, the rest of the data may be crap. Still... it'll do what you want.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[aonefun]

The reason for duplicate contact data in the database is because it is the backend database for our quoting software. Along with each quote is saved the current contact info for the customer. The query of discussion was created by joining the 2 tables that store the quoting documents information.

The reason the data is different for each SoldToCMAcountNo is because one record is saved for each line item (model number quoting on) included in the quote.

>On second thought it would be helpful to query the most recent instance of each SoldToCMAccountNo. This would be indicated by the highest ID value. Would the latter code accomlish this?

Thanks again for your most valuable assistance!

 

[johnwm]

Every time I've ever heard that, it was because the database was not properly normalized.

Along with each quote is saved the current contact info for the customer

aonefun
Have a look at

http://r937.com/relational.html

'Nuff said I feel!

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[gmmastros]

aonefun,

Please do not use the most recent query I posted. If you run it, it will return a recordset, but the problem is that the data may not be what you expect.

Take a close look at an earlier post (20 Dec 07 17:53). In it, I use the min function. If you'd prefer, change it to max instead. I strongly encourage you to study that query. That query uses a method called derived table. This is a very important concept and will allow you to write much better queries.

I encourage you to study the query. Then do a google search [google]derived table[/google] and read a couple pages about derived tables. Once you learn this concept, you will probably be able to write your own query to return the results you want.

If you continue to have problems after doing this research, let me know and I will help you some more.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom