DISTINCT predicate and multiple fields

[dbasch]

Hello,
I am trying to do a query such as:

SELECT DISTINCT [Qualified Leads].[Company], [Qualified Leads].[Record Creator]
FROM [Qualified Leads];

This returns all of the records that have fields taht are a combination of unique values. However, what I really need is just the company field to be distinct. I can acheive this by selecting just the company field. But, what do I do if I want to select distinct companes and also include other fields in the select statement that are not distinct?

Something like this if you pretend the *'s limit the scope of the distinct predicate:

SELECT *DISTINCT [Qualified Leads].[Company]*, [Qualified Leads].[Record Creator]
FROM [Qualified Leads];

Is this possible?

Thanks Everyone,
Derek Basch

 

[swampBoogie]

The problem is that if you have values like

company recordCreator
----------------------
Microsoft ! swampBoogie
Micorsoft ! dbasch

Which value from the recordCreator column should be chosen?

It is possible to get the one with the highest/lowest value

select company,max(recordCreator from qualifiedLeads
group by company

 

[dbasch]

> Which value from the recordCreator column should be chosen?

All of them. Or, all that are distinct.

I think you are thinking of a subquery. DISTINCT is a SELECT predicate and I am not using it in a subquery. I have tried to use subqueries to solve my issue but with no luck. I can only use criteria subqueries because field subqueries can only return a single record.(From documentation):

In a field list, the subquery must return a single value, which is normally assured by using an aggregation such as MAX or SUM.

Example Subquery attempt:

SELECT [Qualified Leads].[Company], [Qualified Leads].[Record Creator]
FROM [Qualified Leads]
WHERE [Qualified Leads].[Company] IN (SELECT DISTINCT [Qualified Leads].[Company] FROM [Qualified Leads]);

This doesn't work because multiple records from the main query can have a company name that is equal to the company name of a distinct record from the subquery.

So, I need to be able to apply the DISTINCT predicate to ONLY ONE FIELD of a simple SELECT statement.

Thanks,
Derek

 

[swampBoogie]

<quote>
So, I need to be able to apply the DISTINCT predicate to ONLY ONE FIELD of a simple SELECT statement.
</quote>

That is not possible. Distinct is always applied to all columns in the select list.

If you want a display that looks like

company recordCreator
----------------------
Microsoft ! swampBoogie, dbasch
Microhard ! aleb, bleb

it is easier to achieve that in the application by using common break logic. I.e. retrieve the values ordered by company and keep track of when the value of company changes.

 

[dbasch]

If you want a display that looks like

company recordCreator
----------------------
Microsoft ! swampBoogie, dbasch
Microhard ! aleb, bleb


I want a display that looks like:

company recordCreator
----------------------
Microsoft ! swampBoogie
Microhard ! dbasch
Microflag ! powlin


That is not possible. Distinct is always applied to all columns in the select list.

it is easier to achieve that in the application by using common break logic. I.e. retrieve the values ordered by company and keep track of when the value of company changes.


I see what you are saying. It looks like I will have to write some VB (which I barely know) to detect when the Company field changes. Where should I start?

Derek