Multiple Distinct Columns

query

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.

Best Answer

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.

Related Solutions

Sql-server – Combine column from multiple rows into single row

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Ms-access – Select DISTINCT records with extra information

SELECT c.*
FROM 
      Customers AS c
   INNER JOIN
      ( SELECT [Address 1], MIN([Order Date]) AS min_od
        FROM Customers
        GROUP BY [Address 1]
      ) AS grp
         ON  grp.[Address 1] = c.[Address 1]
         AND grp.min_od = c.[Order Date] ;

This may still give you two rows for an address, if you have two rows with the same minimum [Order Date] for an address.

Best Answer

Related Solutions

Sql-server – Combine column from multiple rows into single row

Ms-access – Select DISTINCT records with extra information

Related Question