T-SQL Distinct subquery

Question

T-SQL Distinct subquery

slackr13

Mr or Mrs. 500

Points: 565
More actions
February 23, 2005 at 12:57 pm

#60234

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

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

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

slackr13 Mr or Mrs. 500 Points: 565 More actions · Answer 2

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

noeld SSC Guru Points: 96590 More actions · Answer 3

>> 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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

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

slackr13 Mr or Mrs. 500 Points: 565 More actions · Answer 5

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

Paula

noeld SSC Guru Points: 96590 More actions · Answer 6

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