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 0: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 0:58
  • 1
    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 3:16

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy