Multiple Distinct Columns

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.

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.

StackExchange Database Administrators Q#4745, answer score: 4