Question About Eliminating Duplicate Records

[lrdave36]

Hey guys,

I want to know if there is any way to tell SQL to eliminate duplicate records when the result set contains unique values. For example



Code Address
_____ _________


0005 123 Beckham
0005 433 East Hack


Code is my primary key. I would like to see only one record with 0005, but I don't see how this is possible when there are two unique addresses.

How would I tell SQL which record to choose for the output?

 

[gmmastros]

There are things you can do. But, you really need to fix the underlying "problem" with your data. Specifically, if you can have only one value for each code, then you need to enforce this in the database. For example, you could add another column that indicates WHEN the data was changed. Then, you could write some sql that returns the last address (the one with the latest date).

Anyway... you can write a query that only returns one row for each code. SQL has no way of guessing which row you want, so you have to tell it. Something like...

Select Code, Max(Address) As Address
From   YourTable
Group By Code

Instead of max, you could also use MIN. The query above is guaranteed to return just one row for each code, but there is no guarantee that it will return the address you actually want.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

if [tt]code[/tt] is your primary key, you ~cannot~ have more than one row for each value

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

r937

+1.

Exactly.

Also, you should chose what is considered primary address and which address is not needed. How do you decide it?

PluralSight Learning Library

 

[SQLSister]

We store mulitple addresses but mark one as correspondence which is considered the main address. We also have a trigger which enforces that one and only one address is marked as correspondence. That gives us an easy way to know which address to return when we only want one. WE also store address types and those are part of a aunique index with the person id. So the person can have only one home address and only one shipping address and only one business1 address, etc. This also helps to determine which address to chosse when looking for specific types of address.

With no way to know which of the two addresses is the one you want, it is not possible to provide a code solution that is other than randomly picking one. Also be careful if you have multiple address fields in using max() or min(), you don't want the street address from one record paired with the city from the other. Use max or min to pick the address_id (you do have a unique identifier in the record?) and then join to that to get the details of the address.

"NOTHING is more important in a database than integrity." ESquared