#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    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?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    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/

IMN logo majestic logo threadwatch logo seochat tools logo