Select Max date with multiple DATETIME values

Question

I am looking for information from a database to find the latest time in a datetime column. Sometimes there are multiple times a process finishes each day and i only want to see the latest of that day.

I have tried an INNER JOIN statment but i am returning MAX Date.

Data Example below:

Time                 |    Product
2019-01-01-22:15     |    CHEESE
2019-01-01-22:35     |    CHEESE
2019-01-02-22:35     |    CHEESE
2019-01-02-22:37     |    CHEESE

To show as

Time                 |    Product
2019-01-01-22:35     |    CHEESE
2019-01-02-22:37     |    CHEESE

This will be for multiple products

* EDIT *

I need this for multiple dates of the month

* EDIT * It will be for other Products on the day to, Cheese is one example of them

so:

 Time                 |    Product
    2019-01-01-22:15     |    CHEESE
    2019-01-01-22:35     |    CHEESE
    2019-01-01-22:45     |    BREAD
    2019-01-01-22:57     |    BREAD
    2019-01-02-22:35     |    CHEESE
    2019-01-02-22:37     |    CHEESE
    2019-01-02-22:35     |    BREAD
    2019-01-02-22:37     |    BREAD

To show as

Time                 |    Product
2019-01-01-22:35     |    CHEESE
2019-01-01-22:57     |    BREAD
2019-01-02-22:37     |    CHEESE
2019-01-02-22:37     |    BREAD

Answer 1

One option is to use top 1 with ties and row_number with casting:

SELECT TOP 1 WITH TIES [Time], Product
FROM TableName
ORDER BY ROW_NUMBER() OVER(
    PARTITION BY Product, CAST([Time] AS Date) 
    ORDER BY CAST([Time] AS Time) DESC)

The row_number will return 1 for the latest time in each date.

Another option would be to use a common table expression (or a derived table) like this:

WITH CTE AS
(
    SELECT [Time], 
           Product,
           ROW_NUMBER() OVER(
               PARTITION BY Product, CAST([Time] AS Date) 
               ORDER BY CAST([Time] AS Time) DESC) As Rn
    FROM TableName
)
SELECT [Time], Product
FROM CTE
WHERE Rn = 1

This way you can decide how you want to order the results.

Answer 2

Why this is not sufficient ?

select product, max(time)
from table t
group by product, cast(t.time as date);

However, if you have a more columns then you need subquery :

select t.*
from table t
where t.time = (select max(t1.time)
                from table t1
                where cast(t1.time as date) = cast(t.time as date) and
                      t1.product = t.product
               );