Select max value for each company in list, display longest value per company per day

I’ve looked at some of the other questions similar to this, but nothing I’ve found can quite get me there.

I have a table with multiple columns, but 3 of the columns are Company, Date, and WaitTime.

I want to select the longest WaitTime for each company and for each day. I’d like to display the results for the entire date range, showing each company and the longest wait time for that particular day.

Main Table

 Company  |  Date |  WaitTime
 A          11/10     5.2
 A          11/10     5.1
 B          11/10     4.8
 B          11/10     4.7
 A          11/11     5.0
 A          11/11     5.1
 B          11/11     4.6
 B          11/11     4.5
 etc

Desired Results

 Date  |  Company  |  WaitTime
 11/10    A           5.2
 11/10    B           4.8
 11/11    A           5.1
 11/11    B           4.6
 etc

I can have many hundreds of interactions with each company, each day, so I am trying to find the longest wait time for each company for each day.

The most recent version I have tried is this;

 SELECT
 Top 1 with Ties 
 CAST(Timestamp as Date) As Date,
 Campaign as Company,
 (IVR_Time/60.0) as WaitTime

 FROM [Reporting].[dbo].[New_Five9_CallLog] a WITH (NOLOCK)

 Where a.Timestamp >= DATEADD(week, DATEDIFF(week,0,GETDATE())-1,-1)
 AND a.Timestamp < DATEADD(week, DATEDIFF(week,0,GETDATE()),-1)
 AND a.Call_Type = 'Inbound'

 Order By ROW_NUMBER() over (Partition By Campaign Order by Timestamp)

However this isn’t quite showing me what I need.

Answer

You can use window functions. For your sample data, this would look like:

select *
from (
    select t.*, rank() over(partition by company, mydate order by waittime desc) rn
    from mytable t
) t
where rn = 1

date is ambiguous (because there is a datatype that has the same name), so I renamed it to mydate. If that column actually has a time portion, then you need to remove it before ranking, like:

rank() over(partition by company, convert(date, mydate) order by waittime desc) rn

Note that rank() allows top ties, if any.