Trouble understanding DISTINCT

[kilobyte]

Ok, this is a quote from MS Office online.

Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;

If you omit DISTINCT, this query returns both Smith records.

If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.

So, from what I understand of this, if any of the fields in the record (or row, whatever) repeat then it is not displayed. Correct me if I am wrong.

The problem is that when I try and use it in my DB it return all the fields regardless of the Distict clause, unless I am only querying one field, which is next to useless.

For example when I make a test table that has repeating words in one field, with a unique ID for each one, the distinct clause works when I display the words field alone and not when I display the ID field as well.

Can anyone explain what is happening or what I am doing wrong?

 

[Waabs]

I'm not 100% clear about what you're asking but I think this might help:

SELECT DISTINCT Name, ID FROM TestTable

Name ID
John 1
John 2
John 3

The returned records are distinct because no combination of Name and ID is the same.

Hope this helps.

 

[kilobyte]

So does that mean that a record is only omited if ALL the fields are repeated?

 

[Waabs]

Yes. The distinct generally applies to the whole select statement. That's why you can't do something like: Select FirstName, Distinct SecondName, Address, Distinct DOB ...

 

[kilobyte]

I must admit if I COULD do that it would simplify things.

Thankyou for you help and patience, just one more question.

Do you know of a way I can return John only once with his highest ID? Lets say that the table is populated with names and IDs, like the imaginary one, is there a way to return each name once with the highest ID associated with it?

Again, this solution has been eluding me, so if it not possible I wouldn't be surprised.

Thanks again for you help.

 

[neileg]

You could use a Totals query and use Max to select just one record with the desired value.