none
Select multiple distinct columns from a table

    Question

  • Hello,

    I'm using SQL Server 2008 R2 version 10.50.1600.1 in a VB program on an XP Pro, SP3 computer.

    I want to fill three combo boxes in my program, but without duplicates in each of the combo boxes.  I'm not looking to avoid duplicates in the recordset, I just want to keep the combo boxes from having duplicate entries.

    I tried using the following select statement and it did not help.  I still get duplicates in the combo boxes.

    SELECT KeyfilePerson, KeyfileType, ObjectID FROM History GROUP BY KeyfilePerson, KeyfileType, ObjectID

    I have included the information about my database table here:

    Cabinet		varchar(20)	Unchecked
    RequestFrom		varchar(3)	Unchecked
    CustomerNumber	varchar(10)	Unchecked
    Contact		varchar(30)	Checked
    Company		varchar(30)	Checked
    Address1		varchar(30)	Checked
    Address2		varchar(30)	Checked
    Address3		varchar(30)	Checked
    FaxNumber		varchar(20)	Checked
    ObjectID		varchar(50)	Checked
    LetterTool		varchar(20)	Checked
    KeyfileTitle		varchar(30)	Checked
    KeyfileType		varchar(30)	Unchecked
    KeyfilePerson	varchar(30)	Unchecked
    KeyfileKeywords	varchar(50)	Checked
    DateSent		datetime	Unchecked
    SentVia		varchar(5)	Checked
    ProductShipped	datetime	Checked
    NeedUpdate		bit	Checked
    TiffFileDate		datetime	Checked

    Is there a way to eliminate duplicates for each of the three individual fields using an SQL Statement?

    Any help would be gratefully appreciated.

    Thanks,
    Tony


    Stop The World, I want To Get Off!
    Saturday, May 28, 2011 6:39 PM

Answers

  • Why are you not trying three queries which will return distinct data

     

    SELECT KeyfilePerson FROM History GROUP BY KeyfilePerson

    SELECT KeyfileType FROM History GROUP BY KeyfileType

    SELECT ObjectID FROM History GROUP BYObjectID

     

    If you want to return single it in single query you can use this query

    Select Distinct KeyfilePerson ,NULL KeyfileType  ,NULL ObjectID  from History
    Union all
    Select Distinct NULL  ,KeyfileType ,NULL  from History
    Union all
    Select Distinct NULL,NULL,ObjectID  from History

     

    Although it will return all the distinct data but it will include NULL values in it... you have to manipulate some logic fpr checkboxes to avoid NULL values to populate in it....

     


    If this answer is helpful to you .. Please mark as Answer....
    • Marked as answer by Carneno Saturday, May 28, 2011 8:15 PM
    Saturday, May 28, 2011 7:57 PM

All replies

  • Hello Carneno,

    have you checked using Distinct key word as

    SELECT Distinct KeyfilePerson, KeyfileType, ObjectID FROM History 

     

    Thanks,


    Raheel Khan
    Saturday, May 28, 2011 6:51 PM
  • Shouldn't have three recordset, one for each combo? I am not able to visualize the output you are looking for. Can you please elaborate?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Saturday, May 28, 2011 6:51 PM
    Moderator
  • Hello Raheel.

    Yes, I did try the DISTINCT keyword and that gave the same results as my current SELECT.

    Thanks,
    Tony


    Stop The World, I want To Get Off!
    Saturday, May 28, 2011 7:20 PM
  • Hello Balmukund.

    I never stated that i am using three recordsets.  There is only one select statement in my original post.

    I have one table as described in my original post.  That table has 9,000 records in it.  In the three columns that i am selecting, there are duplicates in the table for each of the three columns.  If I use "SELECT KeyfilePerson, KeyfileType, ObjectID FROM History", I will get 9,000 records. 9,000 KeyfilePersons, 9,000 KeyfileTypes and 9,000 ObjectIDs.  Some of the 9,000 KeyfilePersons will consist of duplicates.  Some of the 9,000 KeyfileTypes will consist of duplicates and some of the 9,000 ObjectIDs will consist of duplicates.

    There are 1,000 unique KeyfilePersons out of all the 9,000 KeyfilePersons.
    There are 1,000 unique KeyfileTypes out of all the 9,000 KeyfileTypes.
    There are 1,000 unique ObjectIDs out of all the 9,000 ObjectIDs.

    When I fill my three seperate combo boxes from the data received in my SQL Statement, I want the combo boxes to only have the 1,000 unique entries in the table columns.

    Combox1 will have the 1,000 unique KeyfilePersons.
    Combox2 will have the 1,000 unique KeyfileTypes.
    Combox3 will have the 1,000 unique ObjectIDs.

    I hope this explains further what i want to accomplish.

    Thanks for your help.

    Tony


    Stop The World, I want To Get Off!
    Saturday, May 28, 2011 7:39 PM
  • Why are you not trying three queries which will return distinct data

     

    SELECT KeyfilePerson FROM History GROUP BY KeyfilePerson

    SELECT KeyfileType FROM History GROUP BY KeyfileType

    SELECT ObjectID FROM History GROUP BYObjectID

     

    If you want to return single it in single query you can use this query

    Select Distinct KeyfilePerson ,NULL KeyfileType  ,NULL ObjectID  from History
    Union all
    Select Distinct NULL  ,KeyfileType ,NULL  from History
    Union all
    Select Distinct NULL,NULL,ObjectID  from History

     

    Although it will return all the distinct data but it will include NULL values in it... you have to manipulate some logic fpr checkboxes to avoid NULL values to populate in it....

     


    If this answer is helpful to you .. Please mark as Answer....
    • Marked as answer by Carneno Saturday, May 28, 2011 8:15 PM
    Saturday, May 28, 2011 7:57 PM
  • Thanks Farhan.

    Your single query method worked for me.  I included code to test for NULLs before populating the Comboboxes.

    Thanks for your help.

    Tony


    Stop The World, I want To Get Off!
    Saturday, May 28, 2011 8:17 PM
  • Why don't you normalize your table by spiting into "History" and "Keyprofile". This would enable you to query the table without duplicates.
    Sunday, July 03, 2011 11:15 AM
  • Hello emem0710.

    I'm not sure of what spiting would do for me besides what i am already getting.

    It works fine the way it is.  Is there a reason why i would want to change it now?

    Thanks,
    Tony


    Stop The World, I want To Get Off! Life Isn't About Waiting For The Storm To Pass ... It's About Learning To Dance In The Rain.
    Tuesday, July 05, 2011 2:16 PM