Post reply

unique column

  • March 25, 2004 at 3:36 am

    #162229

    I have a access database table 'CustomerDetails' which has 3 columns CName,CADD,CScore

    Now I want to query this table so that I get only records with all the 3 columns where CName is unique.

    In the table 'CustomerDetails' Cname is not a primary key

    Thanks

  • March 25, 2004 at 3:55 am

    #500389

    Select Distinct (CNAME) From CustomerDetails




    My Blog: http://dineshasanka.spaces.live.com/

  • March 25, 2004 at 4:08 am

    #500392

    But I also want to display CADD,CScore along with CNAME

  • March 25, 2004 at 4:48 am

    #500397

    WHen you add in CADD and CSCORE you will get duplicate names if you have multiple different entries in either of those fields. If you want all the CADD and CSCORE fields but want to group them in you display under a single CNAME then you are talkign about your presentation layer of your application and that has many different potentials based on the applications programming language as to how to go about. If you only want a particular CADD and CSCORE then what criteria decides that and are there more columns involved to check for the criteria?

  • March 25, 2004 at 10:25 pm

    #500579

    Can u put some sample data here




    My Blog: http://dineshasanka.spaces.live.com/

  • March 26, 2004 at 2:02 am

    #500594

    select CName,CADD,CScore

    from CustomerDetails

    where CName in ( Select CName From CustomerDetails group by CName having count(*) = 1)

  • March 27, 2004 at 4:50 pm

    #500803

    If you do not care about the values in CADD and CScore, you can use this query:

    Select CName, max(CADD),max(CScore) from Customerdetails

    group by CName;

    Note : You can use any group function - min or max (or avg for Cscore).

    R_achar

     

     

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply