I am using SQL Server 7

I have a table dbo.Visitors that records all visitors to a company.

The table can hold duplicate entries as the visitor may visit more than once.

I want to show the details but with only one instance of the visitor.

My problem that is with the DISTINCT function I am limited to what fields I can show:

i.e.

Fields:
VisitorID
VisitorName
VisitorCompany

I want to have a distinct function on Name and Company but display the ID as well. When I add all three fields into a view it attempts to run the distinct on all three and I get all results returned. How can I run a distinct on just the Name and the Company but include the ID in the results?

Regards
Vince

Response.write("The best line of code you can ever use"

VBScript | ASP | HTML | SQL | Oracle | Hosting

SELECT DISTINCT(VisitorName), VisitorCompany FROM TABLE

------------------------
Poolio

MSN:

www.eliziumdesign.com

I am having this same problem, but Poolio's solution does not seem to work for me. (I am using access rather than SQL Server).

I still get all rows returned, even if the VisitorName (in this case) is the same.

Any thoughts?

DISTINCT filters out rows that have <b>ALL</b> the same column values.

With fields:
VisitorID
VisitorName
VisitorCompany

if VisitorID is different (which it likely is) then all rows will show.

So..

SELECT DISTINCT VisitorName, VisitorCompany FROM TABLE

Not...

SELECT DISTINCT VisitorID, VisitorName, VisitorCompany FROM TABLE

WDglide,

Thanks for the input. I guess my question is... Is there anyway that the DISTINCT filter can be used when several fields are selected but only only search one field to see if it is distinct.

I will give you a new example of what I am trying to do:

In a table I have 2 fields (ProductID, CategoryID). A product could be listed several times because it may be in more than 1 category. However, I only want this product to be displayed once. Is there anyway that I can perform the distinct filter on JUST the ProductID, but still retrieve the CategoryID for the product?

Hope I explained this well enough...

Yes,

You can do the following:

Select ID, Name, Company
from table
where Company in (Select Distinct Company from table)

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

Edited by - bakerv on 20 Jan 2004 12:52:40