Problem selecting distinct column values.

[mpholt]

I have a query which produces duplicate results often times in a column, and I am trying to figure out how to only return distinct values for a specific column.  The columns in my query are below:

Query A
-------
ID
Name
Category Value
Category Term


Both Category Value and Category Term can contain multiple values for the same ID/Name pair. An example execution is below:

ID - Name - Category Value - Category Term
------------------------------------------
0  - Test - Function       - Protocol
0  - Test - Theme          - Days
1  - Sign - Function       - Protocol
2  - Hist - Theme          - Days
2  - Hist - Time           - Period
2  - Hist - Year           - Present


Basically, I only want the distinct values for the ID column.  Thus, the only information I desire/need is below:

ID
----
0
1
2


I've tried to join the above 'Query A' with another query that just contains the "ID" column (a superset of all available ID's), and then just take the ID column from the result, thinking that it may force a 'select distinct.' However, it didn't. Does anyone know how to implement this?

Thanks, 
   Matt

[Sreeni P]

Dear mpholt...

have u used 'Group' function on ID column? any ways where did u getting this problem..in report layout level?
any ways the group function will eliminate the duplicates on particular column.
                                                                      or
Associate: By associating a grouped data item to another data item we can eliminate duplicate from the report , Both group &Association eliminates duplicates value but we cannot use association alone.. An associated data items hold have a one-to-one relationship with the grouped data item. 

[mpholt]

Dear mpholt...

have u used 'Group' function on ID column? any ways where did u getting this problem..in report layout level?
any ways the group function will eliminate the duplicates on particular column.
                                                                      or
Associate: By associating a grouped data item to another data item we can eliminate duplicate from the report , Both group &Association eliminates duplicates value but we cannot use association alone.. An associated data items hold have a one-to-one relationship with the grouped data item. 

Hi Srinivas,
   Thanks for your repl.  I am running into the issue in the query explorer.  I came across the error when I had a query opened and clicked "Run> View Tabular Data."  However, I can't seem to use the group function within the query itself. Is there anyway to eliminate duplicates within the query? Or do I have to do it on the report itself?

   Thanks again,
      Matthew

[Sreeni P]

mpholt,

If you have more knowledge in Native SQL we can do it by overriding the MDX, if you r using this in report level the best way of approach is grouping and then associating that to with a grouped element

[mpholt]

Hi Srinivas,
   Thanks a lot for your help. Since I was hoping to do this within the query view (since I was using a combination chart instead of a list, and didn't know how to do the grouping from that), I was able to modify my query and remove the "Category Name/Value" data items to the filter, and this removed the duplicates.

    Now I realize that I should have provided more and better information.


    Thanks again for all of your help, I really appreciate it! Best regards!
Matthew