SQL Server – Select Entries Based on Combination of Columns

greatest-n-per-groupsql serversql-server-2008t-sql

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?

Best Answer

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

Related Solutions

Sql-server – Alternative query to this (avoid DISTINCT)

The two scripts in RThomas' answer are both useful. You could also use GROUP BY, which gives a similar advantage to RThomas' methods, but keeping a similar form to your original query.

select country 
from Users inner join
countries on users.CountryID=countries.CountryID
GROUP BY countries.CountryID, countries.country;

The reason why you group by CountryID is that it's the primary key of your countries table, giving the Query Optimizer some better options.

...except that it's not in your scripts.

Put PKs (with Clustered Indexes) on your tables, and a FK relationship between them. Index CountryID in the Users table, and put a Unique Index on the Country field.

Once you've done all that, using DISTINCT how you have will actually give you the ideal execution plan.

PostgreSQL – How to Select the First or Last Entry for a Specific Day

You can't use an aggregate function in a condition, you need to use a sub-select

SELECT EntryID 
FROM AttendanceREcords 
WHERE StaffID = 'xxxxx' 
AND ArrivalTime = (select min(ArrivalTime) 
                   from AttendanceREcords
                   where StaffID = 'xxxxx');

But this can be done more efficiently by using a LIMIT clause:

SELECT EntryID 
FROM AttendanceREcords 
WHERE StaffID = 'xxxxx' 
ORDER BY arrivalTime 
LIMIT 1

There is a difference between the two statements: if more than one row has the same minimum arrival time, the first one will return all of them, the second one only one row.

Another alternative that is usually more efficient that a sub-select is using a window function:

SELECT EntryID
FROM (
  SELECT EntryID, 
         dense_rank() over (order by arrivalTime) as rnk
  FROM AttendanceREcords 
  WHERE StaffID = 'xxxxx' 
) t
where rnk = 1;

By changing the order by arrivalTime you can select the first or the last.

If you want the first and last in a single query you can do something like this:

SELECT EntryID,
       arrivalTime
FROM (
  SELECT EntryID, 
         min(arrivalTime) as min_time,
         min(arrivalTime) as max_time,
  FROM AttendanceREcords 
  WHERE StaffID = 'xxxxx' 
) t
where arrival_time = min_time
   or arrival_time = max_time;

Best Answer

Related Solutions

Sql-server – Alternative query to this (avoid DISTINCT)

PostgreSQL – How to Select the First or Last Entry for a Specific Day

Related Question