Select entries based on a combination of columns

Question

I have a table with a definition like this:

CREATE TABLE [dbo].[Contact]
(
    ClientNumber [numeric] (20, 0) NULL,
    ContactDate [Date] NULL,
    Name [text] NULL,
    AdditionalInformation [text] NULL
)

This table is filled with information for a single month. Each client may have multiple entries for a single day. Meaning there might be entries like this:

42  2015-12-09  Felix       called today
42  2015-12-09  Fexclicx    ...

My goal is to get all client results from the table with the condition that there should be only one entry a day per client number. I do not care which of the (here) two entries is displayed, so something like top 1.

So far I tried to achieve this with a distinct:

select distinct ClientNumber, ContactDate, * from Contact

Unfortunately this does not limit the results to show a client entry for a specific date only once. They still all do appear. Also the problem is that there are no id's in this table so I cannot work with that.

How could I achieve getting all entries but only one entry for one client per day?

Answer 1

To create sample schema and data

 IF OBJECT_ID('tempdb..#Contact', 'U') IS NOT NULL
        DROP TABLE #Contact ;

    CREATE 
    TABLE   #Contact
            (
            ClientNumber [numeric] (20, 0) NULL,
            ContactDate [Date] NULL,
            Name [text] NULL,
            AdditionalInformation [text] NULL
            );

    INSERT
    INTO    #Contact
            (
            ClientNumber
            ,ContactDate
            ,Name
            ,AdditionalInformation
            )

    VALUES  (111,'2016-03-11','A','A'),       
            (111,'2016-03-11','B','B'),
            (112,'2016-03-11','C','C'),       
            (112,'2016-03-12','A','A'),
            (112,'2016-03-13','A','A'),       
            (112,'2016-03-13','B','B'),
            (112,'2016-03-14','A','A'),
            (113,'2016-03-14','B','B'),
            (113,'2016-03-14','C','C');

Actual query

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY ClientNumber, ContactDate ORDER BY ClientNumber DESC) AS RowNumber
               ,ClientNumber
               ,ContactDate
               ,Name
               ,AdditionalInformation
        FROM    #Contact
        )

SELECT  c.RowNumber
       ,c.ClientNumber
       ,c.ContactDate
       ,c.Name
       ,c.AdditionalInformation
FROM    CTERowNumber c
WHERE   c.RowNumber = 1 ;

To return

RowNumber   ClientNumber    ContactDate Name    AdditionalInformation
1           111             2016-03-11  A       A
1           112             2016-03-11  C       C
1           112             2016-03-12  A       A
1           112             2016-03-13  A       A
1           112             2016-03-14  A       A
1           113             2016-03-14  B       B