Quering based on distinct values

[spec]

Hi all,

I had table with following data

Table

f1 f2 f3
1 10 AB
1 11 WW
2 10 DD
2 11 DD

f3 is memo field

I had to retrieve data by grouping records based on f1 value

and i had to display f3 value as "-" if all f3 values for a f1 are distinct.
If all f3 values contains the same value then i should display that value.

I should get output like this

f1 f3
1 -
2 DD


I tried a lot by taking the distinct count of the records and displaying - when count is > 1

but i am not able to succeed.


please any one give the solution.

waiting for your help.


Thanks

 

[mresann]

I'm slightly unclear on some conditions.

Example (f2 omitted for clarity):

f1 f3

1 AA
1 BB

... would mean that you need a field that assigns "-" to 1.

However,

f1 f3
1 AA
1 BB
1 BB

You see that f1, with a value of 1, although f3 is not all the same for 1, there are duplicate instances (BB). Therefore, your condition is not clear to your intention. If I were to guess, I would say that f1 should display the distinct f3 values, even the repeated "BB" field, but that's just a guess.

What is your intention?

 

[spec]

you almost got it

f1 has distinct f3 values in the example you mentioned.
if distinct values are there then f3 value should be displayed as "-" otherwise if f1 has same f3 values then the value of f3 should be displayed.

so for the example you mentioned the output must be

f1 f3
1 -