Multiple Distinct Columns

Question

I have a table named 'DATAENTRY' with the following columns

Title
FName
MName
LName
Add1
Add2
Add3
City

Now I want to select records which have different (FName, MName ,LName) so I tried

SELECT DISTINCT FName, MName, LName FROM dataentry

The problem is that I want full records with all columns but they should be unique across (FName, MName, LName)

I can't use

SELECT DISTINCT Title, FName, MName, LName, Add1, Add2, Add3, city FROM dataentry

because two records with same values in (FName, MName, LName) but with different values in Title or Add1 will be treated as distinct records.

Which database in particular are you looking at? Is this Access, SQL Server, MySQL, Oracle, etc? I ask because it impacts the syntax and usability of the answer. — Richard, Aug 18 '11 at 00:49
I had the same question as @Richard. Whichever database it is, pleaase tell us and we can add the tag for that DB to this question. — RolandoMySQLDBA, Aug 18 '11 at 00:58
In addition to specifying the RDBMS you're using AND the version, can you please explain this: if you have two rows with the same FName, MName and LName, and one has city "Toronto" and the other has city "Chicago", how do you pick which one to return? — Aaron Bertrand, Aug 18 '11 at 03:16

score 4 · Answer 1 · answered Aug 17 '11 at 22:26

Assuming your RDBMS supports windowing functions, something like this will give you what you are asking for:

select * 
from( select dataentry.*, 
             row_number() over (partition by fname, mname, lname order by title) n
      from dataentry )
where n=1;

But note that I have arbitrarily chosen to order by title when discarding 'duplicates' - and even more arbitrarily chosen to overlook that if two matching rows also have matching titles the database will order them in an undefined and possibly unpredictable way. You will need to adjust that to meet your requirements.

Multiple Distinct Columns

1 Answers1