Unique Columns

Question

Unique Columns

David-250683

Hall of Fame

Points: 3452
More actions
June 13, 2007 at 12:30 pm

#124495

Here is a partial result set:
1                      2                 3                           4
5104530010     21840000     20050107001978     Warranty Deed
5104530020     21840000     20050107001978     Warranty Deed
5104530030     21840000     20050107001978     Warranty Deed
Each record is unique but column 3 is the same in each record. I only want to retrieve one record for unique column 3 without regard to other columns. A TOP 1 would work in this small example but not in the full result set because there will be other records retrieved with a different column 3.

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

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

Not 100% sure if this is what you're looking for, but use MAX() on the column you want to use as the differentiator. For instance if you want to use column 1...

SELECTmt.*

FROMMyTable mt

JOIN(

SELECTColumn3, MAX(Column1) as MAX_Col1

FROMMyTable

GROUP BY Column3 ) x

ON mt.Column3 = x.Column3

AND mt.Column1 = x.Max_Col1

David McFarland SSChampion Points: 11815 More actions · Answer 2

If I'm understanding you correctly, even this should work...

SELECT

MaxCol1 = Max(Column1)

,MaxCol2 = Max(Column2)

,Column3

,MaxCol4 = Max(Column4)

FROM

Table

GROUP BY

Column3

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

David's version will create a set of values from different records instead of all of the value's from one record. Don't know if it makes a diffrence to you but could be a potential issue.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

You cannot do any better than David's version unless you know the unique key for the table.

Aaron assumed it's Column1 + Column3, but it's probably not.

Lack of information.

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

I agree that there is a lack of information but Aaron's query will return the values from a record although if column 1 is not unique it may still return more than one record per column3 value. David's could return a mixed bag of values across records. I simply wanted to point out what may not be obvious to everyone.

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 6

Yeah, I was kind of hoping that the original poster with my approach could modify it to get what he was looking for. The reason I use my approach is because in my experience it's usually better to return the values from a single record than from an unknown number of records.