Issue about multiple grouping. How to get a single row from a group?

Question

This is a table with my data:

-----------------------------
|   date   |  value  |  id  |
|03/05/18  |5        | 1    |
|03/05/18  |3        | 2    |
|03/05/18  |5        | 3    |
|03/05/18  |6        | 4    |
|03/05/18  |9        | 5    |
|08/03/19  |5        | 6    |
|08/03/19  |3        | 7    |
|08/03/19  |1        | 8    |
|08/03/19  |6        | 9    |
|01/06/20  |7        | 10   |
|01/06/20  |0        | 11   |
|01/06/20  |2        | 12   |
-----------------------------

I need to find the maximum value in each date and output it with corresponding id. Example:

-----------------------------
|   date   |  value  |  id  |
|03/05/18  |9        | 5    |
|08/03/19  |6        | 9    |
|01/06/20  |7        | 10   |
-----------------------------

Now I know how output the maximum value in each date but without corresponding id. Example:

----------------------
|   date   |  value  |
|03/05/18  |9        |
|08/03/19  |6        |
|01/06/20  |7        |
----------------------

Software I use is MS SQL Server 2012.

My code:

    SELECT 
    date,
    MIN(value)
    
    FROM 
    my_table
    
    GROUP BY date

I've tried the SQL Server function "FIRST_VALUE" but it didn't help.

ALSO I tried to create a comparing condition in a subquery and run into some problems with specifying variables (alias) outside and inside my subquery.

Any ideas, please?

Answer 1

You can filter with a subquery:

select t.*
from mytable t
where t.value = (select max(t1.value) from mytable t1 where t1.date = t.date)

This would allow top ties, if any. Another option is to use window functions:

select *
from (
    select t.*, rank() over(partition by date order by value desc) rn
    from mytable t
) t
where rn = 1

If you want to break ties, you can use row_number() instead of rank() - but to get a stable result, you would need a second column in the order by clause.