T-SQL Distinct subquery

  • Basically, I have a situation where I am getting raw data from an Excel file that contains the following fields vendor_number, bus_name, add1, add2, city, state, zip. The data is brought into an initial table that has no constraints.  I wish to move the data to a table with constraints (constraints being a concatenated primary key--both vendor_number and bus_name) through a DTS package.  I am fine with this part, however, the data is repetitive and I want to get rid of the duplicates.

    For example, if this is the data

    001   Sally    24 west st

    001   Sally     10 South street

    001   Bert     24 west st

    002   Eric      3 Ninth st

    002   Bob       3 Ninth st

    002   Bob       610 fifth st

    For my purposes, I want distinct values for the combination of vendor_number and bus_name.  If I run a select distinct vendor_number, bus_name from <table>,  I get the results that I want--001 Sally, 001 Bert, 002 Eric, and 002 Bob.  However, my difficulty comes into place because I need the rest of the data too--the remaining columns--add1, add2, city, state, zip.  When I enter select distinct vendor_number, bus_name, add1, add2, city, state, zip, the result is every record listed above.  When I attemp to insert this result into the new table, the constraints are violated--unique vendor_number and bus_name (it tries to enter both sally records, violating the constraints).  Is there some subquery or filter that allows you to insert all of the fields into another table based on the uniqueness of the combo of two of those fields?

    Any help is greatly appreciated.  I am new to the whole coding thing and getting data from disparate sources.


    Paula

  • what is happening is that you are mixing things.

    For example lets say you have determined that in the above data 002 and Bob are unique. Which address are you going to use ? the first , the last, ...

    HTH

     


    * Noel

  • For my purposes, the address doesn't really matter--some of the data that will come in will have no address at all or addresses will be spelled differently or will be missing a zip code in one and not the other.  So, basically, as long as I get the vendor_number, bus_name and any one of the addresses, it would be ok. 


    Paula

  • >> So, basically, as long as I get the vendor_number, bus_name and any one of the addresses, it would be ok <<

    Here you go then

     select vendor_number, bus_name, Min(add1), Min(add2), Min(city), Min(state), Min(zip)

     from TableName

     group by vendor_number, bus_name

     

    HTH

     


    * Noel

  • In the example data, there are 2 rows for vendor_number of 001 and bus_name of "Sally"  with the addresss column having values of "24 west st" and "10 South street".  Which one should be used? 

    Assuming the "last", try this solution.  If the "first", change "MAX(InputOrder)" to "MIN(InputOrder)"

    create table VendorAddress_input

    ( vendor_number integer

    , bus_name varchar(255)

    , add1  varchar(255)

    , InputOrder bigint identity(1,1)

    )

    go

    delete from VendorAddress_input

    insert into VendorAddress_input

    (vendor_number , bus_name, add1)

    select 001, 'Sally','24 west st' union all

    select 001, 'Sally','10 South street' union all

    select 001, 'Bert','24 west st' union all

    select 002, 'Eric','3 Ninth st' union all

    select 002, 'Bob','3 Ninth st' union all

    select 002, 'Bob','610 fifth st' union all

    select 003, NULL ,'NO BusinessName 1' union all

    select 003, NULL ,'NO BusinessName 2' union all

    select null , 'Carl', 'NO Vendor 1' union all

    select null , 'Carl', 'NO Vendor 2' union all

    select null , null, 'NO Vendor or business name 1' union all

    select null , null, 'NO Vendor or business name 2'

    go

    select VendorAddress_input.vendor_number

    ,   VendorAddress_input.bus_name

    ,  VendorAddress_input.add1

    from  VendorAddress_input

    join (select MAX(InputOrder)

     from  VendorAddress_input

     where VendorAddress_input.vendor_number is not null

     and  VendorAddress_input.bus_name is not null

     group by VendorAddress_input.vendor_number

     , VendorAddress_input.bus_name

    &nbsp as VALast (InputOrder)

     on VendorAddress_input.InputOrder = VALast.InputOrder

     

    SQL = Scarcely Qualifies as a Language

  • Thanks noeld.  That works perfectly.  Such a simple solution to a problem that has consumed quite a bit of my time.


    Paula

  •  Such a simple solution to a problem that has consumed quite a bit of my time.

    I have been there too, believe me Sometimes I need more coffee


    * Noel

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

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