select distinct problem

Hi,

I want to select distinct values for a specific field and also include values from another field. However, it seems that distinct does not work when I add the second field in select statement. Ie:

select distinct fax, company from data
(does not select distinct fax values)

I have to use the following query to get distinct fax values:
select distinct fax from data

but I also need the company field values returned as well. Is there a way to work around this?

yes, there are several ways to work around this, however, the real difficulty is in stating the problem

suppose you had the following rows:

fax company
123 acme
123 brown
123 zellers
456 foo
456 bar

now obviously you want only two rows returned, but how do you decide which company you want for each fax?

here's one way, based on a criterion that i pulled out of thin air --

select fax, company
from yourtable A
where dateadded =
( select max(dateadded)
from yourtable
where fax = A.fax )


rudy
http://r937.com/